Dynamic Data Validation list

[Roderick_E]

I use this code all the time but it breaks if someone renames the source tab. Any ideas?

=OFFSET(tabname!$J$4,0,0,COUNTIF(tabname!$J$4:$J$65536,"> "))

 

[FDibbins]

Always hard to take operator "adjustments" into account.
Tell them not to change sheet names
protect the sheet so they cant change the name
Create a list of sheet names, and refence that, instead

 

[Roderick_E]

Thanks, I figure it out by using indirect and referring to a cell that will change with the reference/source sheet if renamed:

=OFFSET(INDIRECT("'"&A23&"'!$C$9"),0,0,COUNTIF(INDIRECT("'"&A23&"'!$C$9:$C$65536"),"> "))

 

[Roderick_E]

However, is there way to also return the row number with each element, like

ABC9
DEF10
GHI11
....

I want to use the number to make a combo list Thanks

 

[FDibbins]

Not really sure what you mean in post #4 ?

Also, I noticed that you are using a very large range with that INDIRECT, that could slow your file down a bit. Better to use the range you need (or a few 1000 more than what you think you will need)

 

[Roderick_E]

Not really sure what you mean in post #4 ?

Also, I noticed that you are using a very large range with that INDIRECT, that could slow your file down a bit. Better to use the range you need (or a few 1000 more than what you think you will need)

Thanks for the interaction. Supposedly there is no way to combine multiple columns in a data validation list (I know I could simply make a new column with combined columns but...) so I was thinking if I could get the row number of the single column I AM referencing, then I could use that to concatenate within the actual data validation formula. But since my elements are not always unique, I can't use a match within the data validation formula.

 

[FDibbins]

could you provide some samples and expected outcome please?
(Im a little slow here today lol)

 

[Roderick_E]

could you provide some samples and expected outcome please?
(Im a little slow here today lol)

No worries. Thanks for the interaction.
Suppose in column A2-A10 you had something like ABC, then DEF, GHI... and so on.
In column B2-B10 you had 123, 456, 789... and so on.


To simplify, in the data validation formula, in cell D10 suppose I am using:

=OFFSET(sheet1!$a$2,0,0,COUNTIF(sheet1!$a$2:$a$1000,"> "))

This allows for a dropdown list based on cells A2-A1000.

THE QUESTION
Now for the question. I'm trying to get the data from B2-B10 to show in the dropdown list WITHOUT concatenating A and B in a new column.