Sum function question - easy?

I

Ian in Ankara

Please please help with this simple but long request!!

I have a workbook with multiple named worksheets. In the "summary" worksheet
I have a list of the names of each worksheet. I am trying to use this list to
construct multiple =sum functions each of which refers to a particular named
worksheet, but don't want to type in the name of each worksheet manually.

As an example imagine that there is a worksheet named Leicester and this
name is sitting in cell A1 of the "summary" worksheet. I want to sum the
cells B1 to B5 in the leicester worksheet. I tried to write the following
=SUM(cell("contents",A1)!B1:B5) which does not work.

The problem seems to be how to use the text returned from the cell function
(or t function) as actual text so that it attaches to ! to reference the
appropriate cell.
 
B

Bob Phillips

=SUM(INDIRECT("'"&A1&"'!B1:B5"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
P

Philip J Smith

Hi Ian

See the help on the "Indirect" function
=SUM(INDIRECT(A1&"!"&"b1:b5"))

Regards.
 
I

Ian in Ankara

Thanks very much for your quick response which has already saved me hours of
typing!

Not to be cheeky, but any idea how to do the same thing and avoiding the
"b1:b5" remaing an absolute reference?

Cheers

Ian
 
B

Bob Phillips

Us another cell and store the cells in that

=SUM(INDIRECT("'"&A1&"'!"&B1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
A

Arvi Laanemets

Hi

Another way is to use OFFSET function - but is it usable or not depends on
your data. Something like

=SUM(OFFSET(INDIRECT("'" & A1 & "'!B1"),NumExpr1,NumExpr2,,NumExpr3,))
, where NumExpr1 and NumExpr2 determine the starting cell position
relatively to B1, and NumExpr3 determines the number of cells in summed
range. You are free to use any Excel functions in those expressions, or you
can use a fixed value for any of them. An randomly constructed example:
=SUM(OFFSET(INDIRECT("'" & A1 & "'!B1"),,COLUMN()-2,5,))


Arvi Laanemets
 
Top