using multiple if functions with data validation Closed

Question

Hello, 

Im trying to create a pull down menu with data validation, however for example if M4 = VT-901 then I want to be able to select a size 1080,7628,2113, but the sizes change depending on whats selected in m4. Right now I have the pull down menus with data validation but it is only for one material I have 3 Materials all with 7 Different sizes. The selection is also in a table on a different sheet. Any help would be greatly appreciated!!!

Configuration: Mac OS X Lion (10.7.5) / Safari 6.0.2

Zohaib R · Accepted Answer

Hi chriswiec,

This type of Drop Down list is called as Dynamic Drop Down list. To create this check the link mentioned below:

https://www.youtube.com/watch?v=0Ey8-ILjjYs

Do reply if this helps.

chriswiec · Answer

Thank you Zohaib, I got what I needed for the first part of my problem, now the harder part I believe. So based on what is selected in the pull down of M4  my pull down menu in K13 now pulls up different Material Sizes 2213-60%,1080-54%, 7628-44%. My problem now is in L14 I have a vlookup function that is only looking the thickness of one type of Material from the M4 cell. So what I want to do is if M4's pull down menu says says vt901 then K13's pull down gives me 2213-60%,1080-54%, 7628-44%, if M4's pull down menu says says vt47 then K13's pull down gives me 2213-65%,1080-58%, 7628-40% Based on whats selected in M4 I need L14 to pull up a thickness for each 2213-65% which is different on each material type in M4? I hope that makes semse any help would be great!

Zohaib R · Answer

Hi chriswiec,

Use the below mentioned formula in L14:

= IF(ISNUMBER(SEARCH("2213-60%",K13))=TRUE,VLOOKUP(,,,),"")

Instead of VLOOKUP(,,,) enter your original Vlookup formula.

Please revert for clarification.

chriswiec · Answer

no that doesn't work I need something more like this IF(e2=vt-481(VLOOKUP(B10,'Glass Data'!$A$2:$H$13,3,FALSE)),(IF(e2=vt-47(VLOOKUP(B10,'Glass Data'!$A$2:$H$13,3,FALSE)))))
So if cell e2 equals a certain name, I need the cell i'm writing the formula for to reference a table based on b10 name so if e2=vt-47 then if cell b10 reads 1080 VT-47 it looks up a number in a table in the third column but I have 4 types of material VT-47,VT-42, VT-481, VT-901 so its just reads all the way down with the styes 106 vt-47 then once those are done the table starts into the vt-481 then vt-901. So E2 reads the material type VT-??? based on that b10 reads the style 106-VT47... the in the cell i'm writing the formula I need it to read if 106 VT-47 is selected 1.3? Let me know if that makes sense at all? Thanks for all the help.

106 vt-47             5           1.3
1080 vt-47           5.5        2.1
2213 vt-47           4.5        3.3

Zohaib R · Answer

Hi chriswiec,

Thank you for writing to us. In my previous reply I had mentioned the formula based on your prior explanation. Your requirement now requires me to have a look at the sample data. It is requested that you send the sample data to us. You might want to use the below mentioned site to share the sample file:

https://authentification.site

Note: Please do not share any files that might have any personal or confidential data.

Please revert for clarification.

Using multiple if functions with data validation

5 responses

Similar discussions

Subscribe To Our Newsletter!