Data validation formula

samer-f

New Member
Joined
Apr 16, 2013
Messages
5
Hi, i have been trying to write a formula in the data validation but apparently it doesn't fit. here's the situation:
In D5 i have a drop down list showing 15 descriptions. in E5 i have a another drop down list showing 2 descriptions.
In F5, where i am trying to write the formula, i want it to be a drop down list based on the combination between D5 and E5.

Basically i have 30 lists (15*2) which should change automatically according to the previous cells.I have named the lists "_P1, _P2, .....,_I1, _I2, ....." to minimize the number of characters. All the lists are on a different sheet named "L".
here's the formula:

IF(D5=L!$E$6,IF(E5=L!$B$6,_P2,_I2),IF(D5=L!$E$7,IF(E5=L!$B$6,_P3,_I3),IF(D5=L!$E$8,IF(E5=L!$B$6,_P4,_I4),IF(D5=L!$E$10,IF(E5=L!$B$6,_P6,_I6),IF(D5=L!$E$11,IF(E5=L!$B$6,_P7,_I7),IF(D5=L!$E$12,IF(E5=L!$B$6,_P8,_I8),IF(D5=L!$E$13,IF(E5=L!$B$6,_P9,_I9),IF(D5=L!$E$14,IF(E5=L!$B$6,_P10,_I10),IF(D5=L!$E$15,IF(E5=L!$B$6,_P11,_I11),IF(D5=L!$E$16,IF(E5=L!$B$6,_P12,_I12),IF(D5=L!$E$17,IF(E5=L!$B$6,_P13,_I13),IF(D5=L!$E$18,IF(E5=L!$B$6,_P14,_I14),IF(D5=L!$E$19,IF(E5=L!$B$6,_P15,_I15),IF(D5=L!$E$20,IF(E5=L!$B$6,_P16,_I16),IF(D5=L!$E$22,IF(E5=L!$B$6,_P18,_I18),"")))))))))))))))

is there an alternative way to create this changing drop-down list?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi, i have been trying to write a formula in the data validation but apparently it doesn't fit. here's the situation:
In D5 i have a drop down list showing 15 descriptions. in E5 i have a another drop down list showing 2 descriptions.
In F5, where i am trying to write the formula, i want it to be a drop down list based on the combination between D5 and E5.

Basically i have 30 lists (15*2) which should change automatically according to the previous cells.I have named the lists "_P1, _P2, .....,_I1, _I2, ....." to minimize the number of characters. All the lists are on a different sheet named "L".
here's the formula:

IF(D5=L!$E$6,IF(E5=L!$B$6,_P2,_I2),IF(D5=L!$E$7,IF(E5=L!$B$6,_P3,_I3),IF(D5=L!$E$8,IF(E5=L!$B$6,_P4,_I4),IF(D5=L!$E$10,IF(E5=L!$B$6,_P6,_I6),IF(D5=L!$E$11,IF(E5=L!$B$6,_P7,_I7),IF(D5=L!$E$12,IF(E5=L!$B$6,_P8,_I8),IF(D5=L!$E$13,IF(E5=L!$B$6,_P9,_I9),IF(D5=L!$E$14,IF(E5=L!$B$6,_P10,_I10),IF(D5=L!$E$15,IF(E5=L!$B$6,_P11,_I11),IF(D5=L!$E$16,IF(E5=L!$B$6,_P12,_I12),IF(D5=L!$E$17,IF(E5=L!$B$6,_P13,_I13),IF(D5=L!$E$18,IF(E5=L!$B$6,_P14,_I14),IF(D5=L!$E$19,IF(E5=L!$B$6,_P15,_I15),IF(D5=L!$E$20,IF(E5=L!$B$6,_P16,_I16),IF(D5=L!$E$22,IF(E5=L!$B$6,_P18,_I18),"")))))))))))))))

is there an alternative way to create this changing drop-down list?

Hello and welcome.
Maybe this will be better alternative:
Excel Data Validation -- Dependent Lists
 
Upvote 0
perhaps dv as list and formula:
=INDIRECT(CHOOSE(MATCH(E5,L!$B$6:$B$7,0),"_P","_I") & MATCH(D5,L!$E$6:$E$22,0)+1)

I've assumed E5's DV list is L!B:B7, if not, adjust accordingly.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,207,168
Messages
6,076,907
Members
446,239
Latest member
Home Nest

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