• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - List with Indirect and regular cell/constant value

Amanda92385

New Member
Hello -

I have created a data validation list using indirect off of another cell. That works fine.

=INDIRECT($B$1)

The problem is I want the list to be whatever that indirect formula pulls PLUS the words "Overall Business Line." No matter what values are pulled for the list from the indirect formula I want one of the list values to be "Overall Business Line" - I would prefer it to show as first in the list.

I can't figure out a way to use an indirect with anything else, either a named range, or a specific title etc.

I have searched all over. The file I am working in is massive and I don't want to add any code, I would like to see if I can some how do it within the data validation / list.

Is this possible?

Thanks,
Amanda
 

Debraj

Excel Ninja
Hi Amanda,

How about adding an extra cell (at the top of the Dynamic Validation list..

If B1, is a Name, from Sheet2!A1:A7, why not adding a cell at A2..
 

Amanda92385

New Member
Hi Amanda,

How about adding an extra cell (at the top of the Dynamic Validation list..

If B1, is a Name, from Sheet2!A1:A7, why not adding a cell at A2..
Hi Debraj,

Thanks for your reply!

I should have mentioned that B1 is a named range so it will pull a variety of values, I was trying to avoid having to manually add a cell for each of those named ranges. Also, the list (i.e. A1:A7) being used to populate other areas, so if I add a cell then it will throw off the other areas.

I definitely have workarounds, I can create separate lists so there isn't any issue /crossover but I was hoping there was a way to do it within the data validation box.

Maybe it's not possible, but I thought I would throw it out there :).

Thanks!
Amanda
 

Amanda92385

New Member
Attached is a basic example.

I have the in B3, I want to be able to select either a direct report name or "Overall Business Line" - this will tell all of the tables, graphs and other formulas throughout the document whether this is a report for the Business Leader for his Overall Business Line (i.e. a John Smith's organization) or a report for one of John Smith's direct reports (i.e. John 4)

Thanks!
 

Attachments

jeffreyweir

Active Member
Okay. Quite a few potential options available. But first, can you upload a dummy file with some sample data will be feeding the reports? What I'm trying to ascertain is whether that data is in what's termed a flat file or a crosstab layout. If in a flat file, then we can probably use PivotTables and/or the GETPIVOTDATA function to accomplish your needs. Otherwise we can probably use the CHOOSE function to determine what data source (or what part of the data) gets used to feed the reports and charts.
 

Amanda92385

New Member
Thank you Jeffrey!

All of the direct report names (Jack 1, Clause 1....) are what is feeding to the reports. Then everything else will look at those names to pull additional data. I've uploaded a second file - hopefully this is more helpful.

Unfortunately, I've built a massive reporting template, I'm trying to figure out the best way to do this to avoid having to touch all the other tabs, dynamic graphs, and so forth.

Thank you for all the help!

Amanda
 

Attachments

Top