Forum Discussion

kayem's avatar
kayem
Copper Contributor
Feb 04, 2026

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...
excel
Formulas and Functions
kayem's avatar
kayem
Copper Contributor
Feb 05, 2026

Thanks so much! How do I use the vstack, choosecols, filter to return results from the four sheets in one step? 

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 07, 2026
=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.

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 07, 2026

    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
    • ...