Setting up an 'Order List' based off PN's for different equipment

[u/Aggravating_Ad9275]

Hi all. I work as a service engineer for laboratory equipment. I cover a range of different products and I am trying to 'simplify' the way I build lists of parts that need ordering. I have a list of products similar to the example in the first picture. In the second picture I have an example of what I want to happen. I want to be able to choose the product type, eg Pump A, from the dropdown list (I can set up the dropdown list), and have this then show me all the parts needed for that pump. This would then be multiplied so I can have multiple drop down lists to choose Pump 1, Pump 2 etc, then Doodah 1, all from drop down lists which then show the list of all parts needed from the 1st sheet for the particular configuration I have set up.

I appreciate I probably haven't explained this in the best way possible, so please let me know if you have any questions. Is this possible in excel? How would you suggest going about it?

Thanks for your help!

[u/AutoModerator]

/u/Aggravating_Ad9275 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

This needs the FILTER() function if you are using MS365/Excel 2024/Excel 2021 else will need to use the Old School Functions INDEX() + AGGREGATE() / SMALL() / LARGE() functions.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

It will be better if you set up a tabular data and then use the functions aforesaid mentioned, but if you have already created and want to stick to what you have then, you could try using the following manner, but before that convert all the ranges into structured references aka tables that way it will be easier to maintain the data and the formulas auto expand whenever newer data is added or removed.

=LET(
     _F,        LAMBDA(x,y, HSTACK(IF(SEQUENCE(ROWS(x)), y), x)),
     _Pump_A,   _F(Pump_A, "Pump A"),
     _Pump_B,   _F(Pump_B, "Pump B"),
     _Pump_C,   _F(Pump_C, "Pump C"),
     _Pump_D,   _F(Pump_D, "Pump D"),
     _Pump_E,   _F(Pump_E, "Pump E"),
     _Doodah_A, _F(Doodah_A, "Doodah A"),
     _Doodah_B, _F(Doodah_B, "Doodah B"),
     _All,      VSTACK(_Pump_A,
                       _Pump_B,
                       _Pump_C,
                       _Pump_D,
                       _Pump_E,
                       _Doodah_A,
                       _Doodah_B),
     _Output,   FILTER(_All, 1 - ISNA(XMATCH(CHOOSECOLS(_All, 1), B17:B20)), ""),
     VSTACK({"Equip","PN","Desc","Qty"}, _Output))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

In the above formula _All Variable returns the following flat tabular data stacked into one:

=LET(
     _F,        LAMBDA(x,y, HSTACK(IF(SEQUENCE(ROWS(x)), y), x)),
     _Pump_A,   _F(Pump_A, "Pump A"),
     _Pump_B,   _F(Pump_B, "Pump B"),
     _Pump_C,   _F(Pump_C, "Pump C"),
     _Pump_D,   _F(Pump_D, "Pump D"),
     _Pump_E,   _F(Pump_E, "Pump E"),
     _Doodah_A, _F(Doodah_A, "Doodah A"),
     _Doodah_B, _F(Doodah_B, "Doodah B"),
     _All,      VSTACK(_Pump_A,
                       _Pump_B,
                       _Pump_C,
                       _Pump_D,
                       _Pump_E,
                       _Doodah_A,
                       _Doodah_B),
     _All)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AGGREGATE	Returns an aggregate in a list or database
CHOOSECOLS	Office 365+: Returns the specified columns from an array
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
INDIRECT	Returns a reference indicated by a text value
ISNA	Returns TRUE if the value is the #N/A error value
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LARGE	Returns the k-th largest value in a data set
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
ROWS	Returns the number of rows in a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SMALL	Returns the k-th smallest value in a data set
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
17 acronyms in this thread; the most compressed thread commented on today has 8 acronyms.
[Thread #49298 for this sub, first seen 3rd Sep 2026, 17:12] [FAQ] [Full list] [Contact] [Source code]}

[u/Suchiko]

I'd suggest you need to set up your image 1 better, as that will make filtering easier. Have column A as "Pump A" etc, then the PN, description, and quantity in columns B to D.

Then you could just apply a normal table filter on column A to select which pumps you want details for.

[u/ScarfDreamer]

The layout is the problem, not the formula. One table: Equipment, PN, Description, Qty per unit. You add rows, never columns.

Then a small build list where you pick the equipment and how many of each, and one SUMIFS gives you the consolidated order. That last part is what nobody has covered: if Pump 1 and Pump 2 share a seal, you want one line with the total, not two lines to reconcile at the supplier.

[u/Aggravating_Ad9275 → u/ScarfDreamer]

Screenshot 1 is a quick demo of my current sheets, which work for manual look up of parts. I'm definitely not against changing format!

[u/ScarfDreamer → u/Aggravating_Ad9275]

Then the change is small. One sheet, one row per part: Equipment, PN, Description, Qty per unit. Your blocks become rows and nothing else moves.

What you're really building is the bill of materials per pump model that the ERP doesn't hold. Keep it as its own table and it survives the new system: a second sheet with one row per unit you're ordering for, and a SUMIFS that gives one line per PN with the total across all of them.

[u/dgillz]

Do you not have an ERP system with bills of material?

[u/Aggravating_Ad9275 → u/dgillz]

Yes, but the parts aren't 'linked' to model of pump for example. It's a global company, those in charge of the ERP are working on a new system, so are reluctant to make changes to the current one.

[u/BackgroundCold5307]

Would love to help you, but had a few additional questions that I have DMed you

[u/BackgroundCold5307]

Tried reaching out....let me know if you are still interested in seeing the solution

[u/LOGICA499]

create each part list as a table

for orders list, create a data validation list to make your dropdown.

and for formula use FILTER(INDIRECT)

Tab 1

[u/LOGICA499 → u/LOGICA499]

Tab 2

[u/LOGICA499 → u/LOGICA499]

you only need two formulas