Sum in Across Sheet but Different Cell/Row, but the same column (K)

[muhammad susanto]

hi all...

i have question , how to sum/summary in across sheet in different cell
here my explained :

Excel 2007
	D	E
3	sheet name	cell/last row that contains value
4	sheet1	K10
5	sheet2	K12
6	sheet3	K5
7	sheet4	K20
8	summary/total	?
Sheet5

i hope somebody would give a solution/tricks...

m.susanto

 

[snjpverma]

GLAD TO SEE IT WORKED FOR YOU. Thanks for the like.

hi...

sorry , it's work perfect...i have change

=SUM(Sheet1:Sheet4!XFA1)

thank you very much..

 

[muhammad susanto]

See if this helps
https://www.extendoffice.com/documents/excel/2653-excel-dynamic-list-of-worksheet-names.html#a1

M.

for sheet name is ok, how about get/pull last data?

 

[Marcelo Branco]

Since you have the names of the sheets in, say, D4:D7 you can use the formula below to get the value (instead of the address) of the last cell in column K that contains a number

E4
=INDEX(INDIRECT("'"&D4&"'!K:K"),MATCH(9.99E+307,INDIRECT("'"&D4&"'!K:K")))
copy down

in E8
=SUM(D4:D7)

M.

 

[snjpverma]

@MArcelo, do we need to use the Indirect function with inverted commas "" always ?

 

[Marcelo Branco]

oops...

Formula in E8
=SUM(E4:E7)

M.

 

[Marcelo Branco]

@MArcelo, do we need to use the Indirect function with inverted commas "" always ?

I think you mean with apostrophe "'"
It's necessary (at the beginning and in the end) when the sheet name contains a space like, for example:
'Summary Sheet'

M.

 

[snjpverma]

Thanks Marcelo

 

[Marcelo Branco]

Thanks Marcelo

You are welcome.

M.

 

[Ingolf]

Hi,

In D4:D7 name of sheets
In E4 and down you can use:

=LOOKUP(9.99E+307,INDIRECT("'"&D4&"'!K:K"))

Then sum(E4:E7)