software

Please please help with this simple but long request!!

I have a workbook with multiple named worksheets. In the quot;summaryquot; 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 quot;summaryquot; worksheet. I want to sum the
cells B1 to B5 in the leicester worksheet. I tried to write the following
=SUM(cell(quot;contentsquot;,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(quot;'quot;amp;A1amp;quot;'!B1:B5quot;))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Ian in Ankaraquot; gt; wrote in message
...
gt; Please please help with this simple but long request!!
gt;
gt; I have a workbook with multiple named worksheets. In the quot;summaryquot;
worksheet
gt; I have a list of the names of each worksheet. I am trying to use this list
to
gt; construct multiple =sum functions each of which refers to a particular
named
gt; worksheet, but don't want to type in the name of each worksheet manually.
gt;
gt; As an example imagine that there is a worksheet named Leicester and this
gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum the
gt; cells B1 to B5 in the leicester worksheet. I tried to write the following
gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt;
gt; The problem seems to be how to use the text returned from the cell
function
gt; (or t function) as actual text so that it attaches to ! to reference the
gt; appropriate cell.
gt;
gt;
Hi

=SUM(INDIRECT(quot;'quot; amp; A1 amp; quot;'!B1:B5quot;))--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )quot;Ian in Ankaraquot; gt; wrote in message
...
gt; Please please help with this simple but long request!!
gt;
gt; I have a workbook with multiple named worksheets. In the quot;summaryquot;
gt; worksheet
gt; I have a list of the names of each worksheet. I am trying to use this list
gt; to
gt; construct multiple =sum functions each of which refers to a particular
gt; named
gt; worksheet, but don't want to type in the name of each worksheet manually.
gt;
gt; As an example imagine that there is a worksheet named Leicester and this
gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum the
gt; cells B1 to B5 in the leicester worksheet. I tried to write the following
gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt;
gt; The problem seems to be how to use the text returned from the cell
gt; function
gt; (or t function) as actual text so that it attaches to ! to reference the
gt; appropriate cell.
gt;
gt;
Hi Ian

See the help on the quot;Indirectquot; function
=SUM(INDIRECT(A1amp;quot;!quot;amp;quot;b1:b5quot;))

Regards.

quot;Ian in Ankaraquot; wrote:

gt; Please please help with this simple but long request!!
gt;
gt; I have a workbook with multiple named worksheets. In the quot;summaryquot; worksheet
gt; I have a list of the names of each worksheet. I am trying to use this list to
gt; construct multiple =sum functions each of which refers to a particular named
gt; worksheet, but don't want to type in the name of each worksheet manually.
gt;
gt; As an example imagine that there is a worksheet named Leicester and this
gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum the
gt; cells B1 to B5 in the leicester worksheet. I tried to write the following
gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt;
gt; The problem seems to be how to use the text returned from the cell function
gt; (or t function) as actual text so that it attaches to ! to reference the
gt; appropriate cell.
gt;
gt;

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
quot;b1:b5quot; remaing an absolute reference?

Cheers

Ian

quot;Philip J Smithquot; wrote:

gt; Hi Ian
gt;
gt; See the help on the quot;Indirectquot; function
gt; =SUM(INDIRECT(A1amp;quot;!quot;amp;quot;b1:b5quot;))
gt;
gt; Regards.
gt;
gt; quot;Ian in Ankaraquot; wrote:
gt;
gt; gt; Please please help with this simple but long request!!
gt; gt;
gt; gt; I have a workbook with multiple named worksheets. In the quot;summaryquot; worksheet
gt; gt; I have a list of the names of each worksheet. I am trying to use this list to
gt; gt; construct multiple =sum functions each of which refers to a particular named
gt; gt; worksheet, but don't want to type in the name of each worksheet manually.
gt; gt;
gt; gt; As an example imagine that there is a worksheet named Leicester and this
gt; gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum the
gt; gt; cells B1 to B5 in the leicester worksheet. I tried to write the following
gt; gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt; gt;
gt; gt; The problem seems to be how to use the text returned from the cell function
gt; gt; (or t function) as actual text so that it attaches to ! to reference the
gt; gt; appropriate cell.
gt; gt;
gt; gt;

Us another cell and store the cells in that

=SUM(INDIRECT(quot;'quot;amp;A1amp;quot;'!quot;amp;B1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Ian in Ankaraquot; gt; wrote in message
...
gt; Thanks very much for your quick response which has already saved me hours
of
gt; typing!
gt;
gt; Not to be cheeky, but any idea how to do the same thing and avoiding the
gt; quot;b1:b5quot; remaing an absolute reference?
gt;
gt; Cheers
gt;
gt; Ian
gt;
gt; quot;Philip J Smithquot; wrote:
gt;
gt; gt; Hi Ian
gt; gt;
gt; gt; See the help on the quot;Indirectquot; function
gt; gt; =SUM(INDIRECT(A1amp;quot;!quot;amp;quot;b1:b5quot;))
gt; gt;
gt; gt; Regards.
gt; gt;
gt; gt; quot;Ian in Ankaraquot; wrote:
gt; gt;
gt; gt; gt; Please please help with this simple but long request!!
gt; gt; gt;
gt; gt; gt; I have a workbook with multiple named worksheets. In the quot;summaryquot;
worksheet
gt; gt; gt; I have a list of the names of each worksheet. I am trying to use this
list to
gt; gt; gt; construct multiple =sum functions each of which refers to a particular
named
gt; gt; gt; worksheet, but don't want to type in the name of each worksheet
manually.
gt; gt; gt;
gt; gt; gt; As an example imagine that there is a worksheet named Leicester and
this
gt; gt; gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum
the
gt; gt; gt; cells B1 to B5 in the leicester worksheet. I tried to write the
following
gt; gt; gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt; gt; gt;
gt; gt; gt; The problem seems to be how to use the text returned from the cell
function
gt; gt; gt; (or t function) as actual text so that it attaches to ! to reference
the
gt; gt; gt; appropriate cell.
gt; gt; gt;
gt; gt; gt;
Hi

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

=SUM(OFFSET(INDIRECT(quot;'quot; amp; A1 amp; quot;'!B1quot;),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(quot;'quot; amp; A1 amp; quot;'!B1quot;),,COLUMN()-2,5,))Arvi Laanemets
quot;Bob Phillipsquot; gt; wrote in message
...
gt; Us another cell and store the cells in that
gt;
gt; =SUM(INDIRECT(quot;'quot;amp;A1amp;quot;'!quot;amp;B1))
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Ian in Ankaraquot; gt; wrote in message
gt; ...
gt; gt; Thanks very much for your quick response which has already saved me
hours
gt; of
gt; gt; typing!
gt; gt;
gt; gt; Not to be cheeky, but any idea how to do the same thing and avoiding the
gt; gt; quot;b1:b5quot; remaing an absolute reference?
gt; gt;
gt; gt; Cheers
gt; gt;
gt; gt; Ian
gt; gt;
gt; gt; quot;Philip J Smithquot; wrote:
gt; gt;
gt; gt; gt; Hi Ian
gt; gt; gt;
gt; gt; gt; See the help on the quot;Indirectquot; function
gt; gt; gt; =SUM(INDIRECT(A1amp;quot;!quot;amp;quot;b1:b5quot;))
gt; gt; gt;
gt; gt; gt; Regards.
gt; gt; gt;
gt; gt; gt; quot;Ian in Ankaraquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Please please help with this simple but long request!!
gt; gt; gt; gt;
gt; gt; gt; gt; I have a workbook with multiple named worksheets. In the quot;summaryquot;
gt; worksheet
gt; gt; gt; gt; I have a list of the names of each worksheet. I am trying to use
this
gt; list to
gt; gt; gt; gt; construct multiple =sum functions each of which refers to a
particular
gt; named
gt; gt; gt; gt; worksheet, but don't want to type in the name of each worksheet
gt; manually.
gt; gt; gt; gt;
gt; gt; gt; gt; As an example imagine that there is a worksheet named Leicester and
gt; this
gt; gt; gt; gt; name is sitting in cell A1 of the quot;summaryquot; worksheet. I want to sum
gt; the
gt; gt; gt; gt; cells B1 to B5 in the leicester worksheet. I tried to write the
gt; following
gt; gt; gt; gt; =SUM(cell(quot;contentsquot;,A1)!B1:B5) which does not work.
gt; gt; gt; gt;
gt; gt; gt; gt; The problem seems to be how to use the text returned from the cell
gt; function
gt; gt; gt; gt; (or t function) as actual text so that it attaches to ! to reference
gt; the
gt; gt; gt; gt; appropriate cell.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt;
gt;