Dynamic Data Validation list

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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,"> "))
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
Thanks, I figure it out by using indirect and referring to a cell that will change with the reference/source sheet if renamed:

Code:
=OFFSET(INDIRECT("'"&A23&"'!$C$9"),0,0,COUNTIF(INDIRECT("'"&A23&"'!$C$9:$C$65536"),"> "))
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Last edited:
Upvote 0
could you provide some samples and expected outcome please?
(Im a little slow here today lol)
 
Upvote 0
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:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top