Home Entering a list of values to excel data validation list which already has a validation formula
Reply: 0

Entering a list of values to excel data validation list which already has a validation formula

Sanil Sahadevan
1#
Sanil Sahadevan Published in 2018-02-14 16:10:50Z

I have a data validation formula for creating a dropdown list depending on the value of another cell.

=IF(B2="ES",$K$2:$K$17,IF(B2="FT",$K$18:$K$22,IF(B2="DEV",$K$23,""))) 

Each time the value of Cell B2 changes, the dropdown list updates as per the data in the range.

now i need to change the list of values for condition DEV

for condition ES, the range is from K2 to K17

for condition FT, the range is from K18 to K22... ie a continuation of the range.

both these ranges contain values which are duplicate against each other.

ie for eg.

the range for ES contains values A,B,C,D,E,F...

the range for FT contains values D,E,F,G,H....

now there is overlapping values D,E,F

now my requirement....

in my above formula, if DEV becomes true, then i need a dropdown list that contains all the values in the range. ie from K2 to K22 without duplicate values repeating.

I can create a temporary range for the purpose with the unique values & give the address to that range. But is there another alternative???

the formula above is generated using vba coding. and dynamically updated as per the values in column K against the values ES, FT, DEV which are in column J.

I am attaching the list details here. I have higlighted 1 duplicate value. I have many more of those to be entered

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323886 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO