Sum function question - easy?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
=SUM(INDIRECT("'"&A1&"'!B1:B5"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
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
 
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)
 
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
 
Back
Top