Sum Cell in all Sheets - Without Cell#

[AnetShe]

HI all - any help would be amazing!
I've got a workbook with mulitple sheets and have a 'total' sheet were I am trying to sum the total of a cell across all the sheets.
I am stuck as I want to do this without having to reference that exact cell#, I want to use a table reference (or something?) if possible instead.
The formula I am using is =SUM(Sheet 1:Sheet 5'!AB6).
The AB6 cell is referring to the size XS cell for row# 6 - the issue is that the some columns in the sheet may be deleted, which is throwing off the total sheet.
Is an efficiant formula that can sum the size XS column, row 6 for in all sheets (sheets 1 to 5) without having to reference the cell# (eg. XS6?)

I've had a read and can't see anything - thank you for having a look!!
Let me know if I can help with anymore info.

 

[xlhelp15]

Hi - AnetShe - Welcome to MrExcel !

Yes, this is possible through "Named Ranges" - set the column or cell you wish to reference and sum it through calling it... like the below.

=SUM(XS_1,XS_2,XS_3,XS_4)

Used XS_1 as named range for AB column on each sheets.

If any further help is required, please do share the scenario's for better understanding.

 

[Sufiyan97]

Post some rows of your data using below xl2bb link.

 

[AnetShe]

Hi - AnetShe - Welcome to MrExcel !

Yes, this is possible through "Named Ranges" - set the column or cell you wish to reference and sum it through calling it... like the below.

=SUM(XS_1,XS_2,XS_3,XS_4)

Used XS_1 as named range for AB column on each sheets.

If any further help is required, please do share the scenario's for better understanding.

Is using the name manager something that can be automated? I've got about 100 sizes and up to 2,000 rows for those sizes in a sheet and then could have up to 50 sheets. If it's manual that is a lot of naming!

 

[xlhelp15]

Okay. Have you tried it with INDIRECT function to return the total on your total sheet ?

for example, if you have sheets named : Total, Cal1, Cal2, Cal3 and so on... and each of your sheets holding the same value in AB6 (sum) - then,

Sheets	Values
Cal1	=INDIRECT(E2&"!AB6")
Cal2	=INDIRECT(F2&"!AB6")
Cal3	=INDIRECT(G2&"!AB6")

Here, E2, F2 & G2 referencing the sheets name.

 

[AnetShe]

Post some rows of your data using below xl2bb link.

The form looks like this - all sheets are the same but:
- sheets names order 1 to order 10 have units entered in size column
- I need sheet names Total Orders to bring in the total units into each size column
- I don't want to reference the cell# because columns can be deleted in the sheets (sheet names order 1 to order 10), I want to reference only the column name (eg. XXXS)

 

[Sufiyan97]

See if this helps:

Total Orders:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1	Look Book Order	Category	Style Color	Total Units	Size Scale	XXXS	XXS	XS	S	M	L
2		LW2DPMS				4	2	0	0	0	0
3		LW2DTOS				8	10	0	0	0	0
4		LW1EIBS				0	0	0	0	0	0
5
6
Total Orders

Cell Formulas
Range	Formula
F2:K4	F2	=SUM(SUMPRODUCT(('Order 1'!$B$2:$B$5=$B2)*('Order 1'!$F$1:$K$1=F$1)*'Order 1'!$F$2:$K$5),SUMPRODUCT(('Order 2'!$B$2:$B$5=$B2)*('Order 2'!$F$1:$K$1=F$1)*'Order 2'!$F$2:$K$5))

Order 1

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Look Book Order	Category	Style Color	Total Units	Size Scale	XXXS	XXS	XS	S	M	L
2		LW2DPMS				2	1
3		LW2DTOS				5	6
4		LW1EIBS
5
6
Order 1

Order 2

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1	Look Book Order	Category	Style Color	Total Units	Size Scale	XXXS	XXS	XS	S	M	L
2		LW2DPMS				2	1
3		LW2DTOS				3	4
4		LW1EIBS
5
6
Order 2