SOLVED

Fetch a varying number of records based on criteria, and create table

Frequent Contributor

I would like a sheet where the user fills in a supplier, let's say in C3. Based on this input, a column of products is fetched from a table in another sheet  (Suppliers + Products). The number of products varies according to the supplier.

The user can then fill in quantities next to the products, and a calculation is made.

When the user fills in another supplier, the column with products changes accordingly.

bartvana_2-1626291923478.png

bartvana_3-1626291955016.png

 

What are the different strategies for creating the column with products? See the simplified workbook attached.

 

(I can do this with Power Query, but there I have the problem that upon refresh the references are lost in the formula, see this post "#REF error after Power Query refresh". So I'm interested in other ways to realize the same.)

2 Replies
best response confirmed by bartvana (Frequent Contributor)
Solution

@bartvana 

If you have Excel in Microsoft 365, you can use the FILTER function.

The attached version contains an array formula that will work in all versions of Excel.