Forum Discussion
Formula help
Hi all, I have a spreadsheet with four sheets of data (different suppliers, then organised by catalogue/non-catalogue products), and I want the product name to pull through to a fifth summary sheet...
=LET(colsfromsheets,
CHOOSECOLS(VSTACK(Tabelle1:Tabelle3!A3:J200),2,5,7,10),
IFNA(VSTACK({"Non-Catalogue".""."Catalogue"."",
"Product"."Number Required"."Product"."Number Required"},
HSTACK(
FILTER(CHOOSECOLS(colsfromsheets,1,2),CHOOSECOLS(colsfromsheets,2)>0),
FILTER(CHOOSECOLS(colsfromsheets,3,4),CHOOSECOLS(colsfromsheets,4)>0))),""))Lorenzo has provided a solution for Microsoft 365 in the meantime. I'd have tried to achive the result with the formula above.
Nice one OliverScheurich
Looks like kayem doesn't see the benefits of Structured Tables yet - mentioning the main in case this could make a difference in the future...
- Dynamic ranges (instead of provisioning i.e. 1K rows when 100 are actually used) = efficiency
- Using structured references make formula more comprehensive
- Formulas, cells formatting, conditional formats, data validation… auto. apply to new rows added to the Table
- If the Table Total Row is activated and contains formulas using structured ref., new rows are picked up auto
- ...