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

[Amanda92385]

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]

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]

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

 

[jeffreyweir]

It would help if you could upload a sample file.

 

[Amanda92385]

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!

 

[jeffreyweir]

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]

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