Data Validation Lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Last week I learnt how to

1) populate a data validation list based on the value selected in another list (dependant lists)
2) form dynamic named ranges

from www.contextures.com

Now, I was trying to combine these two ideas to populate a dependant list whose source is dynamic

To this effect, I gave the following reference in the named range, called as "Product"

=OFFSET('Produ code'!$E$2,0,0,COUNTA('Produ code'!$E$2:$E$1000),1

and

in the data validation, gave the following formula

=Indirect(B8) where B8 contains the value, "Product

But the list is not getting populated. Pls tell me where Im going wrong.

Thanks
Josep
 
Hi joseph
I don't think you're doing something wrong. Seems that this is an Excel
restriction. Trying to use INDIRECT on a name which is itself a dynamic
range. This works if you cahnge your name Product to a formula like
='Produ code'!$E$2:$E$10

I tried some workarounds but did not succeed.
Frnak
 
Hi Frank

Thanks for your reply. This really dashes all my plans. I had embarked on a very exciting idea to develop some templates which require the feature.

Well, I still hope that someone would be able to figure this out and try devising a workaround. Ive exhausted my brains for this ..

Thanks once again
Josep

----- Frank Kabel wrote: ----

Hi josep
I don't think you're doing something wrong. Seems that this is an Exce
restriction. Trying to use INDIRECT on a name which is itself a dynami
range. This works if you cahnge your name Product to a formula lik
='Produ code'!$E$2:$E$1

I tried some workarounds but did not succeed
Frna

Joseph wrote
 
Joseph said:
Hi Frank,

Thanks for your reply. This really dashes all my plans. I had
embarked on a very exciting idea to develop some templates which
require the feature.

Well, I still hope that someone would be able to figure this out and
try devising a workaround. Ive exhausted my brains for this ...

Thanks once again,
Joseph
Click to expand...

Hi Joseph
one idea to achieve this:
- use the worksheet_change event
- check if your cell with the data validation range reference is
changed
- if yes, change the data source for the data validation in this macro

Frank
 
Back
Top