Index and dependent dropdown list

Hi guys, I started a thread with similar request. The thread is closed but I need to make additional changes. Here is a link to the original request.
https://www.excelforum.com/excel-for...-criteria.html
John and Siva helped me with first thread, so big thanks to them.
I also posted something similar on another forum but I didn’t get answer, so I am back here and I hope you guys help me out.
So, here we go:
I have a table of products and equipment. Each cell where a product is made on a given equipment is marked “Y” or “‡”. I need your help with 3 formulas:
In column FS: find the name of the product with highest MDD (row 12) and lowest batch size (row 10)
In column FT: how many products are made on a given equipment (excluding duplicate names in E3:FQ3)
I tried COUNTIF(F13:FQ13,"Y")but it is counting everything including duplicate product names. I have duplicate product names because they are made in two sizes.
In the Calculations sheet, I need to create a drop down list dependent on the equipment list in B1 list. So if I select equipment from the first list, the second list should show only those products made on the selected equipment (Again excluding duplicate names).

The worksheet is attached.

Add link to other forum in case you get a reply there.

Originally Posted by JohnTopley

Add link to other forum in case you get a reply there.

Sure John, I will, if I get a reply.

Please add sample expected results

Ok here is the link

https://chandoo.org/forum/threads/in...riteria.36836/

I was trying to follow your logic when you state on the other thread that you don't want to waste people's time, so I figured how linking a thread that doesn't answer the request, would help other people. To me, that's common sense.

Originally Posted by AliGW

it's for the benefit of those trying to help you so they can keep an eye on the other thread and see if anything has been suggested. This prevents unnecessary repetition and means that helpers here do not subsequently discover that they have wasted their time.

Point taken.

try

in FS13

=IF($FR13="","",SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in FT13

=SUM(IF(FREQUENCY(IF($E13:$FQ13<>"", MATCH($E$4:$FQ$4,$E$4:$FQ$4,0)),COLUMN($E13:$FQ13)-COLUMN($E13)+1),1))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks John, I really appreciate it. The formula is FT13 works as intended, but the formula in FS is not.
FS13 needs to show the product name that meets the criteria (max MDD and lowest batch size).

This is the formula I was trying to modify to meet these criteria:
IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")

John.png

In Fs13

=IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

John, thank you sooooo much. It works now as intended
2 formulas of 3 down.

I don't if you would also help me with the dependent drop down list.

I answered that in your previous post where I provided a drop down list of equipment and product list.

Yes John, but you created a drop down menu in A3 . Columns B and G are lists, not dropdown menus and that's exactly what I needed in that sheet. I am trying to create on another sheet two drop down menu, where if I select an equipment from the first dropdown menu, the second drop down menu should list only those products (excluding duplicate product names) that are made on the selected equipment in the first drop down menu. It needs to be a drop down menu, so I can add additional calculations.
dropdown.png

If you put the formula in B2 as your posted file you will get a list , after entering with CSE in B2 and then copying formula down.

I put the first formula in B2 but still getting blank.

John, please keep in mind that my knowledge of Excel formulas is limited to basic functions and I am trying to learn more complicated formulas.

dropdown3.png

Thank AliGW, I did that exactly but I am still getting blank. I am not sure if I am missing something.

Yes, curly brackets appeared around the formula and there are no spaces at the beginning or end.

See attached : Sheet "Calculations"

in D2

=IFERROR(INDEX(Products,MATCH(SUM(COUNTIF(D$1:D1,Products)),COUNTIF(Products,"<"&Products),0)),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

"Products" is named range of data in b2 onward

"Unique_Product_list" is named range of column D

in E2

Data Validation

Allow: =List

Source:

=Unique_Product_LIst

John, two comments.

1. I randomly selected an equipment where are 5 products are made, so the dropdown menu in E1 should show 5 products. In this case, it's showing the entire product list.
2. I need to show in the Calculations two dropdowns only and nothing else because I am going to create more fields below the dropdown menus. So the product list that appears on B2, C2, and D2 onwards, will prevent from creation additional calculation fields below the dropdowns menus.

I hope I am not troubling you. I do appreciate your efforts.


dropdown4.png

This in B2

=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($F$4)+1,""),ROWS($B$2:$B2))),"")


"Equipment_Name" is range B13:B65 in "Trains

"...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hi John and sorry for the late response because we are having a snow storm right now.
I was verifying the formula. I am noticing that second drop down menu is working for some equipment but not all of them.
See for example this. The circled equipment has 7 unique values (products)


dropdownnew.png

But the formula is not showing all products exactly. the product in FQ4 is missing (ART 50mg capsules)

dropdownnew2.png

I do have a suggestion though! Can I merge the cells in the reference sheet to remove the duplicate products, without messing up other fomrmulas?

Typo

in B2

=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH($B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")

Do NOT merge cells: very bad practice.

I created my thread with intention to get help with my request and not waste anyone's time or effort. Props to whoever helped or tried to help me. Adding a reference to another thread that didn't answer the original request doesn't add any value. Plus, once the thread is not on the top threads list, chances of it to go unnoticed/ ignored are high. That's my two cents.

Anyway, for the purpose of complying with the forum rules, here is the link to the other thread https://chandoo.org/forum/threads/he...formula.36870/

For FS13 try

=IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))),$E$8:$FQ$8,0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Re B1 and E1

you can delete Column C

for the other two lists: you can put the formula in other columns and hide the columns OR place them in another sheet but use the same named range names.

Thanks John,

I created a new sheet (CalculationsNew) and created 2 dropdown menus in B1 (name range: Equipment_List) and E1 (name range: Unique_Product_List) but now E1 is stuck on one list and doesn't change according to B1. Can you please have a look at the attached file.

new calculations sheet.jpg

Regarding the new formula in FS 13 IF($FR16="","",INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(($E$8:$FQ$8)*($E$4:$FQ$4=INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E16:$FQ16="y")),($E$12:$FQ$12)*($E16:$FQ16="y"),0)))*(ISNUMBER(SEARCH("s",$E$7:$FQ$7)))),$E$8:$FQ$8,0))), it is showing a product that is not made on equipment row 16


FS13.jpg

Try

=IF($FR13="","",INDEX($E$4:$FQ$4,MATCH(MAX(($E$12:$FQ$12)*($E13:$FQ13="y")),($E$12:$FQ$12)*($E13:$FQ13="y"),0)))

in B2 of "Calculations"

=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$100,MATCH(CalculationsNew!$B$1,Equipment_Name,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$4)+1,""),ROWS($B$2:$B2))),"")


My last one too!

See attached: I "tidied" up the "Calculations" sheet so it only list the "Products" and "Unique Products" based on B1 in "CalculationsNew".

So you only select from B1 in "Calculationsnew" and get the list in E1.

It was always working so I don't understand your reference to E2