How to use concat and indirect to update a sheet range in a sum across the sheets desiginated

[Dobbs1972]

All
I have a project that requires me to sum various activities acroos a range of sheets. Each tab is day and the same activities are in each sheet with the amount of time for that 24hr period.

I understand how to sum across a sheet range but as i add new sheets i have to update all the sheet range formulas. I would like to have a cell for start of my sheet range (blue)and end of my sheet range(green) that is concat (cell E3 yellow) so the value of the cell E3 automatically replaces the hard sheet range in the purple box. That way if i only want to look a whatever range of days i can (based on sheet names)or if i want to sum all sheets for a current total i can. The sum range would be designated by whatever i have in E1 and E2.

please let em know if there is a way using indirect to make this work but this seems like a simple approach but I cant seem to make it work. Thanks for any help and advice in advance-D

 

[Herakles]

All
I have a project that requires me to sum various activities acroos a range of sheets. Each tab is day and the same activities are in each sheet with the amount of time for that 24hr period. View attachment 100962 View attachment 100961
I understand how to sum across a sheet range but as i add new sheets i have to update all the sheet range formulas. I would like to have a cell for start of my sheet range (blue)and end of my sheet range(green) that is concat (cell E3 yellow) so the value of the cell E3 automatically replaces the hard sheet range in the purple box. That way if i only want to look a whatever range of days i can (based on sheet names)or if i want to sum all sheets for a current total i can. The sum range would be designated by whatever i have in E1 and E2. View attachment 100963please let em know if there is a way using indirect to make this work but this seems like a simple approach but I cant seem to make it work. Thanks for any help and advice in advance-D

It would be much easier you keep all of the data in one sheet.

Have a seperate column to indicate the date or day number.

 

[Dobbs1972]

Thanks but this was a dumbed down version to get my point across. Each sheet actuall has many columns of various data. I just simplified it here to try and get some assisstance.

 

[kevin9999]

I don't think you can do it the way you initially thought, but here's one possibility. List all the sheets in column F on your summary sheet. You'll also need a named range (I've called it "days") which is used within the Indirect formula. Select Start and End sheets in E1 & E2.

Book1
	A	B	C	D	E	F
1	Category			Start:	Day 1	Day 1
2	Rest	15		End:	Day 3	Day 2
3	Work					Day 3
4	Eat					Day 4
5	Travel
6	Play
7
Summary

Cell Formulas
Range	Formula
B2	B2	=SUM(INDIRECT("'"&INDIRECT(days)&"'!"&"B2"))

Cells with Data Validation
Cell	Allow	Criteria
E1:E2	List	=$F$1:$F$4

 

[kevin9999]

The formula for the "days" named range is (put in the "Refers to" box in the name manager)

=ADDRESS(MATCH(Summary!$E$1,Summary!$F$1:$F$4,0),6)&":"&ADDRESS(MATCH(Summary!$E$2,Summary!$F$1:$F$4,0),6)

Where the 6 refers to column F

 

[kevin9999]

I think a link to the file would explain it better.
Loading Google Sheets

 

[Fluff]

Another option is to create a defined name ( I called it ShtNames) & in the refers to box put

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

and then you can use

Fluff.xlsm
	A	B	C	D	E	F
1				Start	sheet1	Sheet1
2		30		End	sheet3	Sheet2
3						Sheet3
4
Sheet4

Cell Formulas
Range	Formula
F1:F3	F1	=DROP(TAKE(ShtNames,XMATCH(E2,ShtNames)),XMATCH(E1,ShtNames)-1)
B2	B2	=SUM(INDIRECT(F1#&"!b2"))
Dynamic array formulas.