excel

Get SheetName of Vstack search without helper Column/Row

Trying to show all my Comments of my learning Sessions on one Page. I want to add to the Vstack Filter the Source of the comment without adding a helper column/row

My code so far is as follows:

=SORT(FILTER(VSTACK(Deutsch:PolitikGesellschaftWirtschaft!H6:H10000);
             VSTACK(Deutsch:PolitikGesellschaftWirtschaft!H6:H10000)>""))
Solution

  • Here are two ways to accomplish the desired output, one using helper columns in the source sheets and another using Excel 4.0 macros ( One can read here more on the subject ) - Refer Screenshots and followings to suit with your data.

    enter image description here

    • Option One: - Using Excel Macros 4.0

    1. To use the above method first of all goto Formulas Tab --> Click on Name Manager --> Click New --> Enter SheetNames for the Name: and paste the following formula for the Refers to: option
    =TEXTAFTER(GET.WORKBOOK(1),"]")
    1. Next enter the following formula in the desired sheet for the output:
    =SORT(DROP(REDUCE("",INDIRECT("'"&DROP(SheetNames,,-1)&"'!B3:C10000"),
 LAMBDA(x,y,VSTACK(x,FILTER(y,TAKE(y,,1)>"")))),1))

    Note that from now onwards whenever you add new sheet, it will grab all the data from those sheets returned using SheetNames except the one at the end and which should be at the end, to be ignored as it will be the output sheet, removed using the DROP() function, also one needs to save the files as .xlsb or .xlsm

    • Option Two : Using Helper Columns

    1. Firstly, in an adjacent column to get the sheet names you can either use the following formula or just write down once and copied down
    =TEXTAFTER(CELL("filename",A1),"]")

    1. Repeat the first bullet for the rest of the sheets.

    2. In the output sheet you can use the following formula in order to get the desired output

    =LET(
     α, VSTACK('*'!B3:C10000),
     SORT(FILTER(α, TAKE(α,,1)>"")))

    Or,

    =LET(
     α, VSTACK(Sheet1:Sheet3!B3:C10000),
     SORT(FILTER(α, TAKE(α,,1)>"")))

    Both the formulas will you give desired output, the only difference is the first one uses a Excel 4.0 Macros and LAMBDA() helper function, while the second doesn't and uses helper columns.

    Courtesy: Using the concept of stacking data from multiple sheets using INDIRECT() and REDUCE() taken from this post How to use INDIRECT (or alternative) in an Array into a Matrix answered by VBasic2008 Sir.