Forum Discussion
Fetch a varying number of records based on criteria, and create table
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.
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.)
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.
2 Replies
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.
- OK, thank you!
