Forum Discussion

Microsoft

Jul 17, 2025

Combine table name with each value in the first column

Hello, I need a little help in Excel. So I have some tables of the following structure scattered across multiple sheets. (Fake data) Nokia Phone model Color 6610i Blue 3310 Y...

excel

Formulas and Functions

Harun24HR

Bronze Contributor

Jul 17, 2025

Another way is to use REDUCE() to stack data automatically. In this solution you must enable Excel 4.0 Macros and save the workbook as .x;sm.

First make a named range SheetNames using name manager and following formula-

=TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]")

Then call that named range as REDUCE() function array argument and stack data by in directing those sheets.

=DROP(REDUCE("",DROP(TOCOL(SheetNames),1),LAMBDA(a,x,VSTACK(a,
IFNA(HSTACK(INDIRECT("'"&x&"'!A1"),FILTER(INDIRECT("'"&x&"'!A3:B500"),INDIRECT("'"&x&"'!A3:A500")<>"")),INDIRECT("'"&x&"'!A1"))))),1)

See the attached file.