Many IFS in dependent drop down list.

paphon

New Member
Joined
Jan 29, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have tried in put these ifs in the data validation to make dependent drop down list, however, it only works with 2 statements. After that it doesn't show what I want. And I want to make a lot of ifs.
=IFS(รายการสั่งเหล็ก!B2="ฉาก",OFFSET(Sheet1!$E$1,1,0,COUNTA(OFFSET(Sheet1!$E$1,1,0,30))),รายการสั่งเหล็ก!B2="รางน้ำ",OFFSET(Sheet1!$H$1,1,0,COUNTA(OFFSET(Sheet1!$H$1,1,0,30))),รายการสั่งเหล็ก!B2="รางพับ",OFFSET(Sheet1!$KE$1,1,0,COUNTA(OFFSET(Sheet1!$K$1,1,0,30))))
The table I would like to make is to be able to just click click each size of the steel I wanna order, and each type of steel has it own sizing method.
 

Attachments

  • excel.png
    excel.png
    224.6 KB · Views: 22

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I can see how your database is built up. In the column B you have the types of iron. Then for each type you have a couple of columns that give the various properties of that type. Like HB (cell B5) has its options in columns N & O.
So I am assuming if someone selects HB, then the next dropdown should be columns N & O. Is that correct?

I think I am looking at Sheet1 .

What I think you are trying to do is for each item in column B write a IF() . That is not the way to do it.

When the user selects an item on sheet รายการสั่งเหล็ก, then your formula needs to do a lookup in the sheet1, to find the column. Then create the second dropdown from the column found. That way you don't need all these IFS().

Let me know if you need more help on that.
 
Upvote 0
I can see how your database is built up. In the column B you have the types of iron. Then for each type you have a couple of columns that give the various properties of that type. Like HB (cell B5) has its options in columns N & O.
So I am assuming if someone selects HB, then the next dropdown should be columns N & O. Is that correct?

I think I am looking at Sheet1 .

What I think you are trying to do is for each item in column B write a IF() . That is not the way to do it.

When the user selects an item on sheet รายการสั่งเหล็ก, then your formula needs to do a lookup in the sheet1, to find the column. Then create the second dropdown from the column found. That way you don't need all these IFS().

Let me know if you need more help on that.
You are correct of understanding my workbook. Any suggestion how to do it?
 
Upvote 0
Can you post a screenshot of your sheet รายการสั่งเหล็ก?
I want to understand how you want to use the dropdowns, and where the result of the dropdown selection is placed on the sheet.
Perhaps working with userforms is a neater way to go.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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