Formula to auto populate list

[Ozwilly]

I have a master sheet with a dropdown list. When I select an item from the list, I want to auto populate several lines under this entry. I have tried to use Index Match however cannot work out how to make it select the correct cells.

To give some context, the reference sheet has a list of products and components. In the attached example, on the Reference tab it has Product A 200ml (top level), and then Bottle 200ml, Label 200ml, Shipper 200ml. All of these are components of the top level entry Product A 200ml. The list then continues with Product A 500ml, with the same subset with components related to that product.

On the master sheet, if I select the code 106343 from the dropdown, an Index Match correctly returns the information for Product A 200ml. What I can't figure out is if I make this selection, how do I then automatically populate the Bottle 200ml, Label 200ml & shipper 200ml fields directly under this entry so they don't need to be entered manually?

Thanks!

[63falcondude]

Try this in C5:

=IFERROR(INDEX(Reference!B:B,SMALL(IF(ISNUMBER(SEARCH(VLOOKUP($A$4,Reference!$A:$B,2,0)&"-",Reference!$B$4:$B$16)),ROW(Reference!$A$4:$A$16)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula to the right and down.

[sandy666]

If you are able to use PowerQuery

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

you can try this:

[Ozwilly]

Hi,

Thanks very much. I have tried this formula and even with doing Ctrl Shift Enter cannot make any more than the data in B2 & C2 on the reference sheet appear. i.e only the first line. Not sure if why is not working?? Have reattached with formula included.

Thanks

[Ozwilly]

Hi,

Thanks that is great information. I am not familiar with PowerQuery however am working through it now. Hopefully can install this to make it work, and will be very helpful for future challenges too

[sandy666]

I hope so

[azumi]

Put on B4 :
=VLOOKUP(A4,'Reference sheet'!A22:C24,3,FALSE)

Put this on C4, then copied down and cross:
=IFERROR(INDEX('Reference sheet'!B$4:B$16,SMALL(IF(LOOKUP(ROW('Reference sheet'!$A$4:$A$16),ROW('Reference sheet'!$A$4:$A$16)/('Reference sheet'!$A$4:$A$16<>""),'Reference sheet'!$A$4:$A$16)=$A$4,ROW('Reference sheet'!$B$4:$B$16)-ROW('Reference sheet'!$B$4)+1),ROWS(A$1:A1))),"")

[63falcondude]

In the sample from post #4, you didn't confirm the formula with Ctrl Shift Enter. Once you do, you would see {} around the formula.

[Ozwilly]

Awesome, works beautifully! Much appreciated

[63falcondude]

You're welcome, glad we could help.

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Ozwilly]

Hi,

I am still having trouble with this formula and am hoping someone can assist. I used azumi's formula in post 4 however cannot make it return the values.

Formula in original post was:

=IFERROR(INDEX('Reference sheet'!B$4:B$16,SMALL(IF(LOOKUP(ROW('Reference sheet'!$A$4:$A$16),ROW('Reference sheet'!$A$4:$A$16)/('Reference sheet'!$A$4:$A$16<>""),'Reference sheet'!$A$4:$A$16)=$A$4,ROW('Reference sheet'!$B$4:$B$16)-ROW('Reference sheet'!$B$4)+1),ROWS(A$1:A1))),"")

I have attached an updated sheet to show what I am trying to achieve. It lists the above formula in cell I4 of the Sheet1 tab. I am trying to return the product part codes from specific columns on the 'List' tab when a specific product code is entered in cell B3 on the 'Sheet1' tab.

Thanks

[Bosco]

Hi,

I am still having trouble with this formula and am hoping someone can assist. I used azumi's formula in post 4 however cannot make it return the values.

Formula in original post was:

=IFERROR(INDEX('Reference sheet'!B$4:B$16,SMALL(IF(LOOKUP(ROW('Reference sheet'!$A$4:$A$16),ROW('Reference sheet'!$A$4:$A$16)/('Reference sheet'!$A$4:$A$16<>""),'Reference sheet'!$A$4:$A$16)=$A$4,ROW('Reference sheet'!$B$4:$B$16)-ROW('Reference sheet'!$B$4)+1),ROWS(A$1:A1))),"")

I have attached an updated sheet to show what I am trying to achieve. It lists the above formula in cell I4 of the Sheet1 tab. I am trying to return the product part codes from specific columns on the 'List' tab when a specific product code is entered in cell B3 on the 'Sheet1' tab.

Thanks

Hi,

In H4, CSE formula (confirmed enter with Ctrl+Shift+Enter) copied across to J4 and all copied down :

=IFERROR(INDEX(List!D$4:D$998,SMALL(IF((LOOKUP(ROW(List!$A$4:$A$998),ROW(List!$A$4:$A$998)/(List!$A$4:$A$998<>""),List!$A$4:$A$998)=$A$4)*(List!$E$4:$E$998<>""),ROW(List!$E$4:$E$998)-ROW(List!$E$4)+1),ROWS($A$1:$A1))),"")

Regards
Bosco

[Ozwilly]

Thanks @Bosco,

I tried what you said and it is still not working. I have added the formula into the file to show you. Appreciate your advice on how to fix it!

Rgds

Will

[Ozwilly]

Hi, hoping someone can assist to resolve this for me please?

Thanks

[Bosco]

Thanks @Bosco,

I tried what you said and it is still not working. I have added the formula into the file to show you. Appreciate your advice on how to fix it!

Rgds

Will

Sorry, misread your specification, the criteria should be in Column B and the fixed formula to be as follow :


=IFERROR(INDEX(List!D$4:D$998,SMALL(IF((LOOKUP(ROW(List!$A$4:$A$998),ROW(List!$A$4:$A$998)/(List!$A$4:$A$998<>""),List!$A$4:$A$998)=$B4)*(List!$E$4:$E$998<>""),ROW(List!$E$4:$E$998)-ROW(List!$E$4)+1),ROWS($A$1:$A1))),"")

Regards
Bosco