How to add name of different sheets as a separate column field in vstack formula

[questforexcel]

Hi I referred to the below article for a similar question. But was advised to post this as a new question by a notification on that thread page.

I am currently using the vstack formula to combine multiple sheets together. However, i'd like to include the name of the sheets on the vstack data.

Below is the formula I am currently using. How should I go about editing this to include the sheet name in it.

=VSTACK('Project 1'!$A$2:$X$182,'Project 2'!$A$2:$AG$756,'Project 3'!$A$2:$X$67,'Project 4'!$A$2:$X$137)

I have currently done the first step and created this formula in the name manager section. =TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]"))

I was not able to understand the formula on the below link, so would appreciate if you could help me understand it. Thank you

What does "s" represents in this, why is there a Hstack formula being used in the Vstack formula. Thank you

=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) <> ""))

Name the source of sheetname from Vstack

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

www.mrexcel.com

 

[Fluff]

How about

=LET(a,'Project 1'!$A$2:$X$182,b,'Project 2'!$A$2:$AG$756,c,'Project 3'!$A$2:$X$67,d,'Project 4'!$A$2:$X$137,VSTACK(HSTACK(EXPAND("Project 1",ROWS(a),,"Project 1"),a),HSTACK(EXPAND("Project 2",ROWS(b),,"Project 2"),b),HSTACK(EXPAND("Project 3",ROWS(c),,"Project 3"),c),HSTACK(EXPAND("Project 4",ROWS(d),,"Project 4"),d)))

 

[questforexcel]

How about

=LET(a,'Project 1'!$A$2:$X$182,b,'Project 2'!$A$2:$AG$756,c,'Project 3'!$A$2:$X$67,d,'Project 4'!$A$2:$X$137,VSTACK(HSTACK(EXPAND("Project 1",ROWS(a),,"Project 1"),a),HSTACK(EXPAND("Project 2",ROWS(b),,"Project 2"),b),HSTACK(EXPAND("Project 3",ROWS(c),,"Project 3"),c),HSTACK(EXPAND("Project 4",ROWS(d),,"Project 4"),d)))

Thank you so much!! This worked for me.
Could you help me understand
At the start of the LET formula can the name be any value i'd want to assign , so it could be 1, in place of a?
What does the Hstack formula within the Vstack formula do? Does it expand the rows across the sheet?
Thank you very much!!

 

[Fluff]

You can change the variable names, but they cannot be numbers or valid cell references.
The HSTACK is the same as VSTACK but it stacks the data horizontally rather than vertically.