Name the source of sheetname from Vstack

[dandelion]

Hi,

I am using a Vstack formula to combine data from multiple sheets. Do you know which formula or how I can show in column A the name of sheet that the data was fetched? I don't want to use Power Query. TIA

 

[Anthony47]

Get the formula using FormulaText, such as:

=FORMULATEXT(A2)

Then parse the text you get

But that's a strange request, as who put the formula there should know its parametres; or is it a dynamic range, or a result from other calculations (maybe in a LET)?

 

[Fluff]

What is the formula you are using?

 

[dandelion]

What is the formula you are using?

This is the formula:
=FILTER(VSTACK(SalaryCosts:TravelCosts!B11:N10000),VSTACK(SalaryCosts:TravelCosts!B11:B10000) <> "").
From Salary Costs and Travel Costs, there are multiple Cost sheets as well.

 

[Fluff]

Ok, to start with you need to create a define name, on the Formula tab click Name Manager, New & call is ShtNames & then put this formula in the refers to box

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

Then in the sheet you can use

=LET(s,SEQUENCE(ROWS(ShtNames)),Shts,FILTER(ShtNames,(s>=MATCH("SalaryCosts",ShtNames,0))*(s<=MATCH("TravelCosts",ShtNames,0))),FILTER(VSTACK(HSTACK(Shts,SalaryCosts:TravelCosts!B11:N10000)),VSTACK(SalaryCosts:TravelCosts!B11:B10000) <> ""))

 

[Fluff]

Ignore the sheet formula it's wrong, it should be

=LET(s,SEQUENCE(ROWS(ShtNames)),Shts,FILTER(ShtNames,(s>=MATCH("SalaryCosts",ShtNames,0))*(s<=MATCH("TravelCosts",ShtNames,0))),List,TOCOL(IF(SEQUENCE(,ROWS(B11:B10000)),Shts)),FILTER(HSTACK(List,VSTACK(SalaryCosts:TravelCosts!B11:N10000)),VSTACK(SalaryCosts:TravelCosts!B11:B10000) <> ""))

 

[dandelion]

Ignore the sheet formula it's wrong, it should be

=LET(s,SEQUENCE(ROWS(ShtNames)),Shts,FILTER(ShtNames,(s>=MATCH("SalaryCosts",ShtNames,0))*(s<=MATCH("TravelCosts",ShtNames,0))),List,TOCOL(IF(SEQUENCE(,ROWS(B11:B10000)),Shts)),FILTER(HSTACK(List,VSTACK(SalaryCosts:TravelCosts!B11:N10000)),VSTACK(SalaryCosts:TravelCosts!B11:B10000) <> ""))

Thanks. What does "Shts" refer to?

 

[Fluff]

It's just the name of a variable that holds the filtered array of sheet names.