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

"Philip J Smith" wrote:

Hi Ian

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

Regards.

"Ian in Ankara" wrote:

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.