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.

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ian in Ankara" wrote in message
...
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)

"Ian in Ankara" wrote in message
...
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.

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.

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.

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)

"Ian in Ankara" wrote in message
...
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.

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



"Bob Phillips" wrote in message
...
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)

"Ian in Ankara" wrote in message
...
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.