INDIRECT SUM WITH SHEET NAMES

[occy]

I am trying to sum a particular cell across a range of worksheets. My formula is:

=SUM(INDIRECT(K4&":"&K5&"!"&"J60"))

Where K4 contains the name of the first worksheet and K5 contains the name of the last worksheet in the range I want to sum. J60 is the cell I want to sum.

I get the dreaded #ref! result. Help would be much appreciated.

Thanks

 

[Juan Pablo González]

Hi, welcome to the board !

Do the sheet names contain spaces or 'strange' characters ?

 

[Aladin Akyurek]

I am trying to sum a particular cell across a range of worksheets. My formula is:

=SUM(INDIRECT(K4&":"&K5&"!"&"J60"))

Where K4 contains the name of the first worksheet and K5 contains the name of the last worksheet in the range I want to sum. J60 is the cell I want to sum.

I get the dreaded #ref! result. Help would be much appreciated.

Thanks

You can take a different (faster) route... Insert 2 blank worksheets, name them First and Last, respectively. Place all the relevant worksheets with data between First and Last and use a 3D formula...

=SUM(First:Last!J60)

 

[Juan Pablo González]

And now that I think about it, I don't think that INDIRECT supports the use of 3D formulas.. which is probably why it returns #REF!

 

[occy]

Hey Juan

Wow, thanks for the lightning fast response. There are spaces in the sheet names, how do I compensate for that? Aladin's quick solution of using First and Last sheets will work but I would like to try for something more elegant if possible! If indirect won't work, how do you suggest I acheive my objective?

I found this on j-walk but it also gives me the #ref! error.

WORKSHEET FORMULA TIP

By George Simms

This array formula returns the sum of cells in the 3D range bounded by the
by the sheets named in cells B2 and B3.

=SUM(N(INDIRECT(ROW(INDIRECT(B2&":"&B3))&"!A1")))

Thanks

 

[Juan Pablo González]

That formula seems to work when the sheets are named

1
2
3
etc.

but it's not a true 3D formula because if for example if I have this values:

1!A1 = 1
2!A1 = 2
3!A1 = 3

and they are ordered like

1, 3, 2

then this

=SUM(1:3!A1)

should return 4.

The formula creates an array of numbers from 1 to 3 ({1, 2, 3}) and then that is passed to the INDIRECT function, like this:

{"1!A1","2!A1","3!A1"}

giving in the end a result of 6. I guess Aladin's solution is the easiest... I'll keep looking for a more comprehensive solution tough...

 

[Juan Pablo González]

Ok, I think I have it working...

First, we need to define a name using Insert | Name, Define

Sheets

which refers to:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Now.... I have a workbook with 7 sheets, like this:

"Hoja1","Hoja3","Hoja7","Hoja6","Hoja5","Hoja4","Hoja2"

I disorganized them in purpose !

All have in A1, 1, which I used for testing.

Libro1
	A	B	C	D	E
1	1			Sheets	Hoja1
2
3	FromSheet	Hoja3
4	ToSheet	Hoja2
5	whichcell	A1
6
7	TheSum	6
Hoja1

The array formula in B7 is

=SUM(IF((MATCH(Sheets,Sheets,0)>=MATCH($B$3,Sheets,0))*(MATCH(Sheets,Sheets,0)<=MATCH($B$4,Sheets,0)),N(INDIRECT("'"&Sheets&"'!"&$B$5))))

which could be simplified to

=SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),N(INDIRECT("'"&Sheets&"'!"&$B$5))))

if in C3 you would put

=MATCH(B3,Sheets,0)

and in C4:

=MATCH(B4,Sheets,0)

For some reason that I'm still investigating, it won't produce the correct result if we try to sum, say A1:B1... if anyone has any insight on this, I'd appreaciate it.

 

[Aladin Akyurek]

Ok, I think I have it working...

First, we need to define a name using Insert | Name, Define

Sheets

which refers to:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Now.... I have a workbook with 7 sheets, like this:

"Hoja1","Hoja3","Hoja7","Hoja6","Hoja5","Hoja4","Hoja2"

I disorganized them in purpose !

All have in A1, 1, which I used for testing.

...

The array formula in B7 is

=SUM(IF((MATCH(Sheets,Sheets,0)>=MATCH($B$3,Sheets,0))*(MATCH(Sheets,Sheets,0)<=MATCH($B$4,Sheets,0)),N(INDIRECT("'"&Sheets&"'!"&$B$5))))

which could be simplified to

=SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),N(INDIRECT("'"&Sheets&"'!"&$B$5))))

if in C3 you would put

=MATCH(B3,Sheets,0)

and in C4:

=MATCH(B4,Sheets,0)

For some reason that I'm still investigating, it won't produce the correct result if we try to sum, say A1:B1... if anyone has any insight on this, I'd appreaciate it.

Try...

=SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(9,INDIRECT("'"&Sheets&"'!"&$B$5))))

 

[occy]

Guys

You have lost me. I don't understand the reason for the "sheets" name. What does that do? I copied your method and now get a result (which is progress!) but it is 0 which is not the correct result. I have over 80 sheets, and there are spaces in the sheet names.

Thanks

 

[Juan Pablo González]

Guys

You have lost me. I don't understand the reason for the "sheets" name. What does that do? I copied your method and now get a result (which is progress!) but it is 0 which is not the correct result. I have over 80 sheets, and there are spaces in the sheet names.

Thanks

The GET.WORKBOOK is an old macro formula, an XLM formula. The parameter that you see, the 1 or the 16 is to define what type of information does it return.

The 1 returns an array of all the sheets in the workbook, but including the name of the workbook. The 16 returns the name of the workbook. Then, using the SUBSTITUTE function, Sheets returns an array of only the sheet names.

The array that it created is in E1.

As for the spaces, it shouldn't be a problem, look at this:

Sum 3D reference with Indirect.xls
	A	B	C	D	E
1	1	2		Sheets	Hoja1
2
3	FromSheet	Hoja1	1	=MATCH(B3,Sheets,0)
4	ToSheet	Hoja2	7	=MATCH(B4,Sheets,0)
5	whichcell	A1:B1
6
7	TheSum	7
8		7
9		21
Hoja 1

The formula in B9 is the version that Aladin proposed, that solves the issue that I couldn't !! thanks Aladin for that.