software

Can anyone please help with this function?

I have a large spreadsheet file with numerous worksheets.
Each sheet represents one month of the year and is named Jan05, Feb05, Mar05
etc.

In the quot;Summaryquot; sheet I want to automatically reference a range in the
other sheets.

For example:

In the Summary sheet column A, I have the sheet names:
Jan05
Feb05
Mar05
etc.

In column B, (let's use B5 as an example) I want a formula that reads
something like:
=B4 Apr05!C7

This seems simple enough, but how can I write a formula that 'automatically'
enters the sheet name (i.e. quot;Apr05quot;) in the formula?
The row containing the data for Apr05 is only created when that month comes
along, in order to keep the file size as small as possible at any time.

Thanks,

Coober
use INDIRECT

HTH
--
AP

quot;Billquot; gt; a écrit dans le message de
...
gt; Can anyone please help with this function?
gt;
gt; I have a large spreadsheet file with numerous worksheets.
gt; Each sheet represents one month of the year and is named Jan05, Feb05,
Mar05
gt; etc.
gt;
gt; In the quot;Summaryquot; sheet I want to automatically reference a range in the
gt; other sheets.
gt;
gt; For example:
gt;
gt; In the Summary sheet column A, I have the sheet names:
gt; Jan05
gt; Feb05
gt; Mar05
gt; etc.
gt;
gt; In column B, (let's use B5 as an example) I want a formula that reads
gt; something like:
gt; =B4 Apr05!C7
gt;
gt; This seems simple enough, but how can I write a formula that
'automatically'
gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; The row containing the data for Apr05 is only created when that month
comes
gt; along, in order to keep the file size as small as possible at any time.
gt;
gt; Thanks,
gt;
gt; Coober
gt;
gt;
I've tried using INDIRECT, but cannot find the correct syntax to make the
formula work properly.

If possible, could you please be a little more specific?

Thanks

quot;Ardus Petusquot; gt; wrote in message
...
gt; use INDIRECT
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Billquot; gt; a écrit dans le message de
gt; ...
gt;gt; Can anyone please help with this function?
gt;gt;
gt;gt; I have a large spreadsheet file with numerous worksheets.
gt;gt; Each sheet represents one month of the year and is named Jan05, Feb05,
gt; Mar05
gt;gt; etc.
gt;gt;
gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in the
gt;gt; other sheets.
gt;gt;
gt;gt; For example:
gt;gt;
gt;gt; In the Summary sheet column A, I have the sheet names:
gt;gt; Jan05
gt;gt; Feb05
gt;gt; Mar05
gt;gt; etc.
gt;gt;
gt;gt; In column B, (let's use B5 as an example) I want a formula that reads
gt;gt; something like:
gt;gt; =B4 Apr05!C7
gt;gt;
gt;gt; This seems simple enough, but how can I write a formula that
gt; 'automatically'
gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt;gt; The row containing the data for Apr05 is only created when that month
gt; comes
gt;gt; along, in order to keep the file size as small as possible at any time.
gt;gt;
gt;gt; gt;gt;
gt;
gt;
=B4 INDIRECT(A4amp;quot;!C7quot;)--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Billquot; gt; wrote in message
...
gt; I've tried using INDIRECT, but cannot find the correct syntax to make the
gt; formula work properly.
gt;
gt; If possible, could you please be a little more specific?
gt;
gt; Thanks
gt;
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt; gt; use INDIRECT
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Billquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt;gt; Can anyone please help with this function?
gt; gt;gt;
gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt; gt;gt; Each sheet represents one month of the year and is named Jan05, Feb05,
gt; gt; Mar05
gt; gt;gt; etc.
gt; gt;gt;
gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in the
gt; gt;gt; other sheets.
gt; gt;gt;
gt; gt;gt; For example:
gt; gt;gt;
gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt; gt;gt; Jan05
gt; gt;gt; Feb05
gt; gt;gt; Mar05
gt; gt;gt; etc.
gt; gt;gt;
gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that reads
gt; gt;gt; something like:
gt; gt;gt; =B4 Apr05!C7
gt; gt;gt;
gt; gt;gt; This seems simple enough, but how can I write a formula that
gt; gt; 'automatically'
gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; gt;gt; The row containing the data for Apr05 is only created when that month
gt; gt; comes
gt; gt;gt; along, in order to keep the file size as small as possible at any time.
gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Try this:

=B4 INDIRECT(A4amp;quot;!C7quot;)

With Apr05 in A4.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Billquot; gt; wrote in message
...
gt; I've tried using INDIRECT, but cannot find the correct syntax to make the
gt; formula work properly.
gt;
gt; If possible, could you please be a little more specific?
gt;
gt; Thanks
gt;
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt; gt; use INDIRECT
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Billquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt;gt; Can anyone please help with this function?
gt; gt;gt;
gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt; gt;gt; Each sheet represents one month of the year and is named Jan05, Feb05,
gt; gt; Mar05
gt; gt;gt; etc.
gt; gt;gt;
gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in the
gt; gt;gt; other sheets.
gt; gt;gt;
gt; gt;gt; For example:
gt; gt;gt;
gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt; gt;gt; Jan05
gt; gt;gt; Feb05
gt; gt;gt; Mar05
gt; gt;gt; etc.
gt; gt;gt;
gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that reads
gt; gt;gt; something like:
gt; gt;gt; =B4 Apr05!C7
gt; gt;gt;
gt; gt;gt; This seems simple enough, but how can I write a formula that
gt; gt; 'automatically'
gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; gt;gt; The row containing the data for Apr05 is only created when that month
gt; gt; comes
gt; gt;gt; along, in order to keep the file size as small as possible at any time.
gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;Thanks, that works well! However, I also need to SUM a range which would
include 2 INDIRECT references:

In this case, I would need to have the two references inside a SUM function.

I tried this:
=SUM(INDIRECT(A2amp;quot;.B2quot;):INDIRECT(A4amp;quot;.B2quot;))
where A2 contains Jan06 amp; A4 contains Mar06
and a few variations, but only get an error.

The idea here is to sum the values in Jan06!B2:Mar06!B2

Is this possible?

Thanks

quot;Ragdyerquot; gt; wrote in message
...
gt; Try this:
gt;
gt; =B4 INDIRECT(A4amp;quot;!C7quot;)
gt;
gt; With Apr05 in A4.
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Billquot; gt; wrote in message
gt; ...
gt;gt; I've tried using INDIRECT, but cannot find the correct syntax to make the
gt;gt; formula work properly.
gt;gt;
gt;gt; If possible, could you please be a little more specific?
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; use INDIRECT
gt;gt; gt;
gt;gt; gt; HTH
gt;gt; gt; --
gt;gt; gt; AP
gt;gt; gt;
gt;gt; gt; quot;Billquot; gt; a écrit dans le message de
gt;gt; gt; ...
gt;gt; gt;gt; Can anyone please help with this function?
gt;gt; gt;gt;
gt;gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt;gt; gt;gt; Each sheet represents one month of the year and is named Jan05, Feb05,
gt;gt; gt; Mar05
gt;gt; gt;gt; etc.
gt;gt; gt;gt;
gt;gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in
gt;gt; gt;gt; the
gt;gt; gt;gt; other sheets.
gt;gt; gt;gt;
gt;gt; gt;gt; For example:
gt;gt; gt;gt;
gt;gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt;gt; gt;gt; Jan05
gt;gt; gt;gt; Feb05
gt;gt; gt;gt; Mar05
gt;gt; gt;gt; etc.
gt;gt; gt;gt;
gt;gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that reads
gt;gt; gt;gt; something like:
gt;gt; gt;gt; =B4 Apr05!C7
gt;gt; gt;gt;
gt;gt; gt;gt; This seems simple enough, but how can I write a formula that
gt;gt; gt; 'automatically'
gt;gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt;gt; gt;gt; The row containing the data for Apr05 is only created when that month
gt;gt; gt; comes
gt;gt; gt;gt; along, in order to keep the file size as small as possible at any
gt;gt; gt;gt; time.
gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
=INDIRECT(A2amp;quot;!B2quot;) INDIRECT(A4amp;quot;!B2quot;)--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Billquot; gt; wrote in message
...
gt; Thanks, that works well! However, I also need to SUM a range which would
gt; include 2 INDIRECT references:
gt;
gt; In this case, I would need to have the two references inside a SUM
function.
gt;
gt; I tried this:
gt; =SUM(INDIRECT(A2amp;quot;.B2quot;):INDIRECT(A4amp;quot;.B2quot;))
gt; where A2 contains Jan06 amp; A4 contains Mar06
gt; and a few variations, but only get an error.
gt;
gt; The idea here is to sum the values in Jan06!B2:Mar06!B2
gt;
gt; Is this possible?
gt;
gt; Thanks
gt;
gt; quot;Ragdyerquot; gt; wrote in message
gt; ...
gt; gt; Try this:
gt; gt;
gt; gt; =B4 INDIRECT(A4amp;quot;!C7quot;)
gt; gt;
gt; gt; With Apr05 in A4.
gt; gt; --
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; quot;Billquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I've tried using INDIRECT, but cannot find the correct syntax to make
the
gt; gt;gt; formula work properly.
gt; gt;gt;
gt; gt;gt; If possible, could you please be a little more specific?
gt; gt;gt;
gt; gt;gt; Thanks
gt; gt;gt;
gt; gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; use INDIRECT
gt; gt;gt; gt;
gt; gt;gt; gt; HTH
gt; gt;gt; gt; --
gt; gt;gt; gt; AP
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Billquot; gt; a écrit dans le message de
gt; gt;gt; gt; ...
gt; gt;gt; gt;gt; Can anyone please help with this function?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt; gt;gt; gt;gt; Each sheet represents one month of the year and is named Jan05,
Feb05,
gt; gt;gt; gt; Mar05
gt; gt;gt; gt;gt; etc.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in
gt; gt;gt; gt;gt; the
gt; gt;gt; gt;gt; other sheets.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; For example:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt; gt;gt; gt;gt; Jan05
gt; gt;gt; gt;gt; Feb05
gt; gt;gt; gt;gt; Mar05
gt; gt;gt; gt;gt; etc.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that
reads
gt; gt;gt; gt;gt; something like:
gt; gt;gt; gt;gt; =B4 Apr05!C7
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; This seems simple enough, but how can I write a formula that
gt; gt;gt; gt; 'automatically'
gt; gt;gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; gt;gt; gt;gt; The row containing the data for Apr05 is only created when that
month
gt; gt;gt; gt; comes
gt; gt;gt; gt;gt; along, in order to keep the file size as small as possible at any
gt; gt;gt; gt;gt; time.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt;
gt;
Surely this simply adds 2 cells.

What I need to do is to sum the range i.e. B2 on each of the following
sheets:
Jan06
Feb06
Mar06

I want to create a range that includes B2 for every sheet between the two
indirect references. However, as this often contains dozens of references, I
don't want to simply use quot; quot; between each.
quot;Bob Phillipsquot; gt; wrote in message
...
gt; =INDIRECT(A2amp;quot;!B2quot;) INDIRECT(A4amp;quot;!B2quot;)
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Billquot; gt; wrote in message
gt; ...
gt;gt; Thanks, that works well! However, I also need to SUM a range which would
gt;gt; include 2 INDIRECT references:
gt;gt;
gt;gt; In this case, I would need to have the two references inside a SUM
gt; function.
gt;gt;
gt;gt; I tried this:
gt;gt; =SUM(INDIRECT(A2amp;quot;.B2quot;):INDIRECT(A4amp;quot;.B2quot;))
gt;gt; where A2 contains Jan06 amp; A4 contains Mar06
gt;gt; and a few variations, but only get an error.
gt;gt;
gt;gt; The idea here is to sum the values in Jan06!B2:Mar06!B2
gt;gt;
gt;gt; Is this possible?
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt; quot;Ragdyerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Try this:
gt;gt; gt;
gt;gt; gt; =B4 INDIRECT(A4amp;quot;!C7quot;)
gt;gt; gt;
gt;gt; gt; With Apr05 in A4.
gt;gt; gt; --
gt;gt; gt; HTH,
gt;gt; gt;
gt;gt; gt; RD
gt;gt; gt;
gt;gt;
gt;gt; --------------------------------------------------------------------------
gt; -
gt;gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
gt; !
gt;gt;
gt;gt; --------------------------------------------------------------------------
gt; -
gt;gt; gt; quot;Billquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; I've tried using INDIRECT, but cannot find the correct syntax to make
gt; the
gt;gt; gt;gt; formula work properly.
gt;gt; gt;gt;
gt;gt; gt;gt; If possible, could you please be a little more specific?
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; use INDIRECT
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; HTH
gt;gt; gt;gt; gt; --
gt;gt; gt;gt; gt; AP
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Billquot; gt; a écrit dans le message de
gt;gt; gt;gt; gt; ...
gt;gt; gt;gt; gt;gt; Can anyone please help with this function?
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt;gt; gt;gt; gt;gt; Each sheet represents one month of the year and is named Jan05,
gt; Feb05,
gt;gt; gt;gt; gt; Mar05
gt;gt; gt;gt; gt;gt; etc.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range in
gt;gt; gt;gt; gt;gt; the
gt;gt; gt;gt; gt;gt; other sheets.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; For example:
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt;gt; gt;gt; gt;gt; Jan05
gt;gt; gt;gt; gt;gt; Feb05
gt;gt; gt;gt; gt;gt; Mar05
gt;gt; gt;gt; gt;gt; etc.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that
gt; reads
gt;gt; gt;gt; gt;gt; something like:
gt;gt; gt;gt; gt;gt; =B4 Apr05!C7
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; This seems simple enough, but how can I write a formula that
gt;gt; gt;gt; gt; 'automatically'
gt;gt; gt;gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt;gt; gt;gt; gt;gt; The row containing the data for Apr05 is only created when that
gt; month
gt;gt; gt;gt; gt; comes
gt;gt; gt;gt; gt;gt; along, in order to keep the file size as small as possible at any
gt;gt; gt;gt; gt;gt; time.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
Sorry, Saturday blues.

You can't do it quite as you want, you will need to list the sheets in
A2:A13, and just setup the formula for those you want, like so

=SUMPRODUCT(N(INDIRECT(quot;'quot;amp;A2:A4amp;quot;'!B2quot;)))

which will do Jan, Feb and Mar

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Billquot; gt; wrote in message
...
gt; Surely this simply adds 2 cells.
gt;
gt; What I need to do is to sum the range i.e. B2 on each of the following
gt; sheets:
gt; Jan06
gt; Feb06
gt; Mar06
gt;
gt; I want to create a range that includes B2 for every sheet between the two
gt; indirect references. However, as this often contains dozens of references,
I
gt; don't want to simply use quot; quot; between each.
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; =INDIRECT(A2amp;quot;!B2quot;) INDIRECT(A4amp;quot;!B2quot;)
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Billquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Thanks, that works well! However, I also need to SUM a range which
would
gt; gt;gt; include 2 INDIRECT references:
gt; gt;gt;
gt; gt;gt; In this case, I would need to have the two references inside a SUM
gt; gt; function.
gt; gt;gt;
gt; gt;gt; I tried this:
gt; gt;gt; =SUM(INDIRECT(A2amp;quot;.B2quot;):INDIRECT(A4amp;quot;.B2quot;))
gt; gt;gt; where A2 contains Jan06 amp; A4 contains Mar06
gt; gt;gt; and a few variations, but only get an error.
gt; gt;gt;
gt; gt;gt; The idea here is to sum the values in Jan06!B2:Mar06!B2
gt; gt;gt;
gt; gt;gt; Is this possible?
gt; gt;gt;
gt; gt;gt; Thanks
gt; gt;gt;
gt; gt;gt; quot;Ragdyerquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Try this:
gt; gt;gt; gt;
gt; gt;gt; gt; =B4 INDIRECT(A4amp;quot;!C7quot;)
gt; gt;gt; gt;
gt; gt;gt; gt; With Apr05 in A4.
gt; gt;gt; gt; --
gt; gt;gt; gt; HTH,
gt; gt;gt; gt;
gt; gt;gt; gt; RD
gt; gt;gt; gt;
gt; gt;gt;
gt;
gt;gt; -------------------------------------------------------------------------
-
gt; gt; -
gt; gt;gt; gt; Please keep all correspondence within the NewsGroup, so all may
benefit
gt; gt; !
gt; gt;gt;
gt;
gt;gt; -------------------------------------------------------------------------
-
gt; gt; -
gt; gt;gt; gt; quot;Billquot; gt; wrote in message
gt; gt;gt; gt; ...
gt; gt;gt; gt;gt; I've tried using INDIRECT, but cannot find the correct syntax to
make
gt; gt; the
gt; gt;gt; gt;gt; formula work properly.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; If possible, could you please be a little more specific?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Thanks
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; use INDIRECT
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; HTH
gt; gt;gt; gt;gt; gt; --
gt; gt;gt; gt;gt; gt; AP
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; quot;Billquot; gt; a écrit dans le message de
gt; gt;gt; gt;gt; gt; ...
gt; gt;gt; gt;gt; gt;gt; Can anyone please help with this function?
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt; gt;gt; gt;gt; gt;gt; Each sheet represents one month of the year and is named Jan05,
gt; gt; Feb05,
gt; gt;gt; gt;gt; gt; Mar05
gt; gt;gt; gt;gt; gt;gt; etc.
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a range
in
gt; gt;gt; gt;gt; gt;gt; the
gt; gt;gt; gt;gt; gt;gt; other sheets.
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; For example:
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt; gt;gt; gt;gt; gt;gt; Jan05
gt; gt;gt; gt;gt; gt;gt; Feb05
gt; gt;gt; gt;gt; gt;gt; Mar05
gt; gt;gt; gt;gt; gt;gt; etc.
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that
gt; gt; reads
gt; gt;gt; gt;gt; gt;gt; something like:
gt; gt;gt; gt;gt; gt;gt; =B4 Apr05!C7
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; This seems simple enough, but how can I write a formula that
gt; gt;gt; gt;gt; gt; 'automatically'
gt; gt;gt; gt;gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; gt;gt; gt;gt; gt;gt; The row containing the data for Apr05 is only created when that
gt; gt; month
gt; gt;gt; gt;gt; gt; comes
gt; gt;gt; gt;gt; gt;gt; along, in order to keep the file size as small as possible at any
gt; gt;gt; gt;gt; gt;gt; time.
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
If you change the sheet names, to say 0601, 0602, 0603, etc, you could do it
with

=SUMPRODUCT(N(INDIRECT(quot;'quot;amp;TEXT(ROW(INDIRECT(H2amp;quot;: quot;amp;H4)),quot;0000quot;)amp;quot;'!B2quot;)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; Sorry, Saturday blues.
gt;
gt; You can't do it quite as you want, you will need to list the sheets in
gt; A2:A13, and just setup the formula for those you want, like so
gt;
gt; =SUMPRODUCT(N(INDIRECT(quot;'quot;amp;A2:A4amp;quot;'!B2quot;)))
gt;
gt; which will do Jan, Feb and Mar
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Billquot; gt; wrote in message
gt; ...
gt; gt; Surely this simply adds 2 cells.
gt; gt;
gt; gt; What I need to do is to sum the range i.e. B2 on each of the following
gt; gt; sheets:
gt; gt; Jan06
gt; gt; Feb06
gt; gt; Mar06
gt; gt;
gt; gt; I want to create a range that includes B2 for every sheet between the
two
gt; gt; indirect references. However, as this often contains dozens of
references,
gt; I
gt; gt; don't want to simply use quot; quot; between each.
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; =INDIRECT(A2amp;quot;!B2quot;) INDIRECT(A4amp;quot;!B2quot;)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Billquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt;gt; Thanks, that works well! However, I also need to SUM a range which
gt; would
gt; gt; gt;gt; include 2 INDIRECT references:
gt; gt; gt;gt;
gt; gt; gt;gt; In this case, I would need to have the two references inside a SUM
gt; gt; gt; function.
gt; gt; gt;gt;
gt; gt; gt;gt; I tried this:
gt; gt; gt;gt; =SUM(INDIRECT(A2amp;quot;.B2quot;):INDIRECT(A4amp;quot;.B2quot;))
gt; gt; gt;gt; where A2 contains Jan06 amp; A4 contains Mar06
gt; gt; gt;gt; and a few variations, but only get an error.
gt; gt; gt;gt;
gt; gt; gt;gt; The idea here is to sum the values in Jan06!B2:Mar06!B2
gt; gt; gt;gt;
gt; gt; gt;gt; Is this possible?
gt; gt; gt;gt;
gt; gt; gt;gt; Thanks
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Ragdyerquot; gt; wrote in message
gt; gt; gt;gt; ...
gt; gt; gt;gt; gt; Try this:
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; =B4 INDIRECT(A4amp;quot;!C7quot;)
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; With Apr05 in A4.
gt; gt; gt;gt; gt; --
gt; gt; gt;gt; gt; HTH,
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; RD
gt; gt; gt;gt; gt;
gt; gt; gt;gt;
gt; gt;
gt;
gt;gt; -------------------------------------------------------------------------
gt; -
gt; gt; gt; -
gt; gt; gt;gt; gt; Please keep all correspondence within the NewsGroup, so all may
gt; benefit
gt; gt; gt; !
gt; gt; gt;gt;
gt; gt;
gt;
gt;gt; -------------------------------------------------------------------------
gt; -
gt; gt; gt; -
gt; gt; gt;gt; gt; quot;Billquot; gt; wrote in message
gt; gt; gt;gt; gt; ...
gt; gt; gt;gt; gt;gt; I've tried using INDIRECT, but cannot find the correct syntax to
gt; make
gt; gt; gt; the
gt; gt; gt;gt; gt;gt; formula work properly.
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; If possible, could you please be a little more specific?
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; Thanks
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt; gt; gt;gt; gt;gt; ...
gt; gt; gt;gt; gt;gt; gt; use INDIRECT
gt; gt; gt;gt; gt;gt; gt;
gt; gt; gt;gt; gt;gt; gt; HTH
gt; gt; gt;gt; gt;gt; gt; --
gt; gt; gt;gt; gt;gt; gt; AP
gt; gt; gt;gt; gt;gt; gt;
gt; gt; gt;gt; gt;gt; gt; quot;Billquot; gt; a écrit dans le message de
gt; gt; gt;gt; gt;gt; gt; ...
gt; gt; gt;gt; gt;gt; gt;gt; Can anyone please help with this function?
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; I have a large spreadsheet file with numerous worksheets.
gt; gt; gt;gt; gt;gt; gt;gt; Each sheet represents one month of the year and is named Jan05,
gt; gt; gt; Feb05,
gt; gt; gt;gt; gt;gt; gt; Mar05
gt; gt; gt;gt; gt;gt; gt;gt; etc.
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; In the quot;Summaryquot; sheet I want to automatically reference a
range
gt; in
gt; gt; gt;gt; gt;gt; gt;gt; the
gt; gt; gt;gt; gt;gt; gt;gt; other sheets.
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; For example:
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; In the Summary sheet column A, I have the sheet names:
gt; gt; gt;gt; gt;gt; gt;gt; Jan05
gt; gt; gt;gt; gt;gt; gt;gt; Feb05
gt; gt; gt;gt; gt;gt; gt;gt; Mar05
gt; gt; gt;gt; gt;gt; gt;gt; etc.
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; In column B, (let's use B5 as an example) I want a formula that
gt; gt; gt; reads
gt; gt; gt;gt; gt;gt; gt;gt; something like:
gt; gt; gt;gt; gt;gt; gt;gt; =B4 Apr05!C7
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; This seems simple enough, but how can I write a formula that
gt; gt; gt;gt; gt;gt; gt; 'automatically'
gt; gt; gt;gt; gt;gt; gt;gt; enters the sheet name (i.e. quot;Apr05quot;) in the formula?
gt; gt; gt;gt; gt;gt; gt;gt; The row containing the data for Apr05 is only created when that
gt; gt; gt; month
gt; gt; gt;gt; gt;gt; gt; comes
gt; gt; gt;gt; gt;gt; gt;gt; along, in order to keep the file size as small as possible at
any
gt; gt; gt;gt; gt;gt; gt;gt; time.
gt; gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt; gt;
gt; gt; gt;gt; gt;gt; gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;