question about many sheets in one warkbook
 

Hi,
I want to thank to everyone who help me for my last difficulty with
excel.
My question is:How can I sum numbers from different sheets with one
address. But these sheets must be selected by the name in one cell,
again with address, which is the same for all sheets. For example: I
have 50 sheets in one warkbook, but only in 5 there is a name KAI, in
cell with adress(a5). I want to sum the numbers from this sheets with
name KAI from the address h31.
Thank you in advance.
Excuse me for my bad english ;)


--
tzveti
------------------------------------------------------------------------
tzveti's Profile: http://www.excelforum.com/member.php...o&userid=32566
View this thread: http://www.excelforum.com/showthread...hreadid=546772

question about many sheets in one warkbook
 

I'm not sure if Excel allows you to simply drill the array on the z
dimension (rows, columns, sheets = x, y, z). If there are no other
suggestions, you could try something in VBA like:

Public Function SumIfZCore(strToMatch as string) as Double
Dim ws as Worksheet, numTemp as Double
For Each ws in ActiveWorkbook.Worksheets
If ws.Range("A5").Text = strToMatch Then numTemp = numTemp +
ws.Range("H31").Value
Next ws
SumIfZCore = numTemp
End Function

Not sure if you can use Range like that without selecting the sheet,
but you get the idea.

Then in a cell you can type =SumIfZCore("KAI"), and it will give you
the answer. You could expand the custom function to specify what
address to look-in and what address(es) to sum as well.

HTH