Data validation without a static named range

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello, all:

First time post-er. Thx, in advance, for any and all assistance.

I'm working in MS Excel 2010. I am trying to create a drop-down list for a cell. But, all examples I have seen require me to identify a named range as the source of data for the drop-down list.

I would like something a little more robust.

Specifically, I would like the drop-down list to look at Sheet2, look for all the rows where Column H equals (for example) "Shade Tree". Then, have the drop-down list show the contents of Column B for each of those rows that meet the "Shade Tree" criterion.

(Column B contains the specific name of the tree species. Column H just contains the type "Shade tree".)

Any input would be appreciated. Thx,

Cliff



1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
one way of doing it is
1.select column H
2.autofilter with criteria as "sahde tree"
3.copy the filtered data into another columns and name it
3.sue this named range as validation list

remember column H must have column heading

of course you can have a macro for this if you are familiar with macros
0