Microsoft Excel

[DanAuber]

I have a column of cells which also are worksheet names. Thus:

Water
Roading
Stormwater
etc...

I want to put a formula into the adjacent column to sum the column K in each case in the relevant sheet - but I don't know how to reference the worksheet in the formula.

For example, if the word Water is in cell A1 I am typing into B1 =sum(A1!K:K) and wanting it to sum column K in the sheet called water. But when I click OK on the formula it opens a browse window and asks me to find workBOOK A3 ! There is obviously something clever I need to do with the syntax - can anyone help ?

 

[petrosky]

Hi DanAuber,

What you are looking for is the INDIRECT() function I believe.

=SUM(INDIRECT("'" & A1&"'!K:K"))

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice