Data validation formula

samer-f

New Member
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

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:
Hi p45cal,

I just tried your formula as well, it WORKS!
thanks.

Replies
1
Views
197
Replies
13
Views
468
Replies
6
Views
289
Replies
7
Views
350
Replies
13
Views
499

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.

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

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