Change formula in Data Validation based on cell selection

[Me22]

I have need to change a formula in a Data Validation list based on the selection in another cell.

As an example, I have a table setup that has 4 columns and 8 rows including headers:

The Data Validation is part of the selection process to pull a record from an array. The dropdown list is generated from the formula in the Name "Select" and currently is:
OFFSET(!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1,1).

This give a dropdown of Column C and allows me to select a record by name.

I also have a Data Validation list in the Search By which generates it's list from the Name "Search_By" which references the table headers.

I want to alter the OFFSET formula for "Select" in the Data Validation when I make a selection in the "Search By" dropdown. So if I select "City" in stead of "Name", the formula would be:
OFFSET(!$D$1,1,0,COUNTA(Sheet1!$D:$D)-1,1).

I've tried referring to the Cell "Search By" is in, B13, but no go. I've also tried to use INDIRECT in various ways, with no success.

So how do I alter that formula in Data Validation through the use of a dropdown selection in a cell? Can't figure it out.

 

[mumps]

Have a look at this link: Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial]

 

[jasonb75]

As you have a fairly simple table, using an index formula ' might' work, but the known method involves named ranges and indirect references.

Try

=INDEX(Sheet1!$B$2:$E$8,0,MATCH(Sheet1!$B$13,Sheet1!$B$1:$E$1,0))

If that doesn't work then I suggest looking at some of the in depth articles that explain how to set up dependent validation lists, such as the one that @mumps has suggested, or the one here,

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[Me22]

Ah, I should have mentioned that the actual table has 112 columns and 200 rows (at the moment-- added to regularly). I don't know that I will ever need to search by each of the 40 headers, but that flexibility would be nice.

 

[Me22]

Ah, I should have mentioned that the actual table has 112 columns and 200 rows (at the moment-- added to regularly). I don't know that I will ever need to search by each of the 40 headers, but that flexibility would be nice.

The 40 headers that matter most I mean.

 

[jasonb75]

A different method, assuming that column B contains only numbers below the header and that all columns will be equal in length.

=OFFSET(Sheet1!$B$2,,MATCH(Sheet1!$B$13,Sheet1!$1:$1,0)-COLUMN($B$2),MATCH(1e+100,Sheet1!$B:$B),1)

I personally prefer to use index rather than offset, but it can't be used with data validation if the size of the source list is variable.

 

[Me22]

Hello again. So I have gone through the options given and have several different solutions that can work. Just have to settle on one.
Thanks so much for the help. I have another issue now but will go to a new thread.

 

[Peter_SSs]

Could this alternative approach be any use to you?

1. Turn the data table into a formal Excel table by selecting it, including headings, then Insert ribbon tab -> Table (may be slightly different in Excel 2010 as I can't remember)
   
   
   
   
   
   
2. For you DV in 'Select' no need for a name definition, just use this in the DV List dialog
   
   
   

If you change the value in the B13 drop-down, the 'Select' drop-down changes to suit.

 

[Me22]

Thank you all for the feedback and direction. I have to do a bit of a rethink on this it seems. My columns will not always have data in every cell. That is complicating it more. I'm thinking I may have to use powerquery to generate lists to make it easier. I'll post when/if I work out how to go forward.

 

[jasonb75]

If you mean that there could be intermittent gaps then it can still be done with formulas, but it can get messy.

To do it and keep the lists tidy, you would need to duplicate every list with formulas to filter out the blanks, then use the formula generated lists to populate the dropdowns.

If this is something you want to look into then I'll put an example together sometime over the weekend, unless Peter does it first.