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.

 

[Me22]

Before I posted yesterday I started looking for the solution and everything looked so complicated, and frankly, possibly beyond me. That's why I started thinking about PowerQuery.

However, my feeling is that leaving PowerQuery out of the picture until it is really necessary for analyzing things is better. (It always seems to make the workbooks go slower. (I don't have any factual justification for that, and if I'm wrong someone please tell me.)

So, Yes. If you are willing to give the formulas way a try, I'd certainly appreciate that.

 

[jasonb75]

My columns will not always have data in every cell.

Could you clarify ta couple of things first?

Will the empty cells always be at the bottom of the list, of could there be empty cells in the middle? i.e. data in C2:C4 and C6:C10 but C5 empty.

Are the empty cells really empty, or do they contain formulas that show blanks?

Having re-read your original post, I think that Peter's suggestion in post 8 will be the best method for you to use, or perhaps a variation of it to allow for the blanks, but that would depend on the outcome of the points that I have asked you to clarify.

 

[Me22]

The empty cells could occur anywhere in the column, not just the beginning or the end. It could be a gap of one cell or many
I had not thought about formulas. There re some such columns in a few of the tables I am working. However, they are at the end of the tables and I have added them for the purpose of pivot tables that interpret some of the data, or for resetting some checkboxes to false when the data is recalled to the form to be viewed. They (to a high degree of certainly) would not be columns I would use in the way we are talking about. So I think it best to go ahead assuming no cells with formulas are involved at this time.
Does that Help?

 

[Peter_SSs]

See if this would suffice.

1. For any columns you might want to use for Data validation, add an extra column to the table and use identical headings but append "DV" to each heading.
   
   
2. Add the formulas shown to the top cell in each of these additional columns and the results should spill down the column.
   
   
3. These additional columns can now be hidden if you want.
   
   
4. Add the Data validation as shown. Note that I have assumed the main columns are not formulas. However, that may not be the case. If formulas are or could be involved in the main data area (columns B:E below) then use this formula in the DV for cell D16 instead of the one shown below the mini-sheet.
   
   =OFFSET(INDIRECT("Table1["&B13&"DV]"),0,0,COUNTIF(INDIRECT("Table1["&B13&"DV]"),"?*")+COUNT(INDIRECT("Table1["&B13&"DV]")))

Me22 2020-03-08 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Number	Name	City	Division	NumberDV	NameDV	CityDV	DivisionDV
2		10	Name 1	City 1		10	Name 1	City 1	Div 4
3		11	Name 2	City 2		11	Name 2	City 2	Div 5
4		12				12	Name 5	City 4	Div 6
5		13		City 4	Div 4	13	Name 6	City 6	Div 7
6		14	Name 5		Div 5	14		City 7
7		15	Name 6	City 6	Div 6	15
8		16		City 7	Div 7	16
9
10
11
12
13		City
14
15
16
17
DV Lists

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX([Number],AGGREGATE(15,6,(ROW([Number])-ROW(Table1[#Headers]))/([Number]<>""),ROW()-ROW(Table1[#Headers]))),"")
G2:G8	G2	=IFERROR(INDEX([Name],AGGREGATE(15,6,(ROW([Name])-ROW(Table1[#Headers]))/([Name]<>""),ROW()-ROW(Table1[#Headers]))),"")
H2:H8	H2	=IFERROR(INDEX([City],AGGREGATE(15,6,(ROW([City])-ROW(Table1[#Headers]))/([City]<>""),ROW()-ROW(Table1[#Headers]))),"")
I2:I8	I2	=IFERROR(INDEX([Division],AGGREGATE(15,6,(ROW([Division])-ROW(Table1[#Headers]))/([Division]<>""),ROW()-ROW(Table1[#Headers]))),"")

Cells with Data Validation
Cell	Allow	Criteria
D16	List	=OFFSET(INDIRECT("Table1["&B13&"DV]"),0,0,COUNTA(INDIRECT("Table1["&B13&"]")))
B13	List	=$B$1:$E$1