Re: Data Validation - Dynamic Ranges
Quote from danred99;562358
The data validation formula is: =OFFSET(INDEX(INDIRECT(C1),2),0,0,COUNTA(INDIRECT(C1)),1)
See my attached example
Although this thread can be considered as "Archeology" I would warmly suggest to use the following formula in the Data Validation cell D1:
instead of what you have presented.
I'm sure you will, easily, find the difference by carefully checking the values in the drop-down list D1 when rng1 is selected in cell C1.
Now, let us take it one step farther.
The main issue is, mainly, to keep dynamic ranges to allow the user to add items to rng1 and rng2.
If you'll declare both of them as "LIST" they will, automatically, became dynamic and then the DV can simply refer to:
The user can add as many items he wants to both "Lists" to immediatelly be reflected in the drop-down list of DV cell D1.
The picture will help you out.