User Profile
Harun24HR
Bronze Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Combine table name with each value in the first column
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) Download Link.
