How to create a 'dynamic' formula?

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 "Summary" 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. "Apr05") 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

"Bill" <bstemp@leschambres.co.za> a écrit dans le message de
news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> 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 "Summary" 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. "Apr05") 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
>
>

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

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> use INDIRECT
>
> HTH
> --
> AP
>
> "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
>> 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 "Summary" 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. "Apr05") 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.
>>
>> >>
>
>

=B4 + INDIRECT(A4&"!C7")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill" <bstemp@leschambres.co.za> wrote in message
news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> 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
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> > use INDIRECT
> >
> > HTH
> > --
> > AP
> >
> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> >> 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 "Summary" 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. "Apr05") 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.
> >>
> >> >>
> >
> >
>
>

Try this:

=B4+INDIRECT(A4&"!C7")

With Apr05 in A4.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bill" <bstemp@leschambres.co.za> wrote in message
news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> 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
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> > use INDIRECT
> >
> > HTH
> > --
> > AP
> >
> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> >> 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 "Summary" 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. "Apr05") 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, 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(A2&".B2"):INDIRECT(A4&".B2"))
where A2 contains Jan06 & 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

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> Try this:
>
> =B4+INDIRECT(A4&"!C7")
>
> With Apr05 in A4.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Bill" <bstemp@leschambres.co.za> wrote in message
> news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
>> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
>> > use INDIRECT
>> >
>> > HTH
>> > --
>> > AP
>> >
>> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
>> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
>> >> 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 "Summary" 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. "Apr05") 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.
>> >>
>> >> >>
>> >
>> >
>>
>>
>

=INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill" <bstemp@leschambres.co.za> wrote in message
news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
> 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(A2&".B2"):INDIRECT(A4&".B2"))
> where A2 contains Jan06 & 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
>
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> > Try this:
> >
> > =B4+INDIRECT(A4&"!C7")
> >
> > With Apr05 in A4.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> >> 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
> >>
> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> >> > use INDIRECT
> >> >
> >> > HTH
> >> > --
> >> > AP
> >> >
> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> >> >> 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 "Summary" 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. "Apr05") 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.
> >> >>
> >> >> >>
> >> >
> >> >
> >>
> >>
> >
>
>

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 "+" between each.



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OXqrszgZGHA.1196@TK2MSFTNGP03.phx.gbl...
> =INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bill" <bstemp@leschambres.co.za> wrote in message
> news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
>> 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(A2&".B2"):INDIRECT(A4&".B2"))
>> where A2 contains Jan06 & 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
>>
>> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
>> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
>> > Try this:
>> >
>> > =B4+INDIRECT(A4&"!C7")
>> >
>> > With Apr05 in A4.
>> > --
>> > HTH,
>> >
>> > RD
>> >
>>
>> --------------------------------------------------------------------------
> -
>> > Please keep all correspondence within the NewsGroup, so all may benefit
> !
>>
>> --------------------------------------------------------------------------
> -
>> > "Bill" <bstemp@leschambres.co.za> wrote in message
>> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
>> >> 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
>> >>
>> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
>> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
>> >> > use INDIRECT
>> >> >
>> >> > HTH
>> >> > --
>> >> > AP
>> >> >
>> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
>> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
>> >> >> 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 "Summary" 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. "Apr05") 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.
>> >> >>
>> >> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>>
>
>

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("'"&A2:A4&"'!B2")))

which will do Jan, Feb and Mar

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill" <bstemp@leschambres.co.za> wrote in message
news:efPvIwhZGHA.1200@TK2MSFTNGP03.phx.gbl...
> 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 "+" between each.
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OXqrszgZGHA.1196@TK2MSFTNGP03.phx.gbl...
> > =INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
> >> 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(A2&".B2"):INDIRECT(A4&".B2"))
> >> where A2 contains Jan06 & 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
> >>
> >> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> >> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> >> > Try this:
> >> >
> >> > =B4+INDIRECT(A4&"!C7")
> >> >
> >> > With Apr05 in A4.
> >> > --
> >> > HTH,
> >> >
> >> > RD
> >> >
> >>
>
>> -------------------------------------------------------------------------
-
> > -
> >> > Please keep all correspondence within the NewsGroup, so all may
benefit
> > !
> >>
>
>> -------------------------------------------------------------------------
-
> > -
> >> > "Bill" <bstemp@leschambres.co.za> wrote in message
> >> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> >> >> 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
> >> >>
> >> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> >> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> >> >> > use INDIRECT
> >> >> >
> >> >> > HTH
> >> >> > --
> >> >> > AP
> >> >> >
> >> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> >> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> >> >> >> 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 "Summary" 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. "Apr05") 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.
> >> >> >>
> >> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
> >
>
>

If you change the sheet names, to say 0601, 0602, 0603, etc, you could do it
with

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(H2&":"&H4)),"0000")&"'!B2")))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O2GDU5iZGHA.3524@TK2MSFTNGP04.phx.gbl...
> 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("'"&A2:A4&"'!B2")))
>
> which will do Jan, Feb and Mar
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bill" <bstemp@leschambres.co.za> wrote in message
> news:efPvIwhZGHA.1200@TK2MSFTNGP03.phx.gbl...
> > 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 "+" between each.
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:OXqrszgZGHA.1196@TK2MSFTNGP03.phx.gbl...
> > > =INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
> > >> 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(A2&".B2"):INDIRECT(A4&".B2"))
> > >> where A2 contains Jan06 & 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
> > >>
> > >> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> > >> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> > >> > Try this:
> > >> >
> > >> > =B4+INDIRECT(A4&"!C7")
> > >> >
> > >> > With Apr05 in A4.
> > >> > --
> > >> > HTH,
> > >> >
> > >> > RD
> > >> >
> > >>
> >
>
>> -------------------------------------------------------------------------
> -
> > > -
> > >> > Please keep all correspondence within the NewsGroup, so all may
> benefit
> > > !
> > >>
> >
>
>> -------------------------------------------------------------------------
> -
> > > -
> > >> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > >> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> > >> >> 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
> > >> >>
> > >> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> > >> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> > >> >> > use INDIRECT
> > >> >> >
> > >> >> > HTH
> > >> >> > --
> > >> >> > AP
> > >> >> >
> > >> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> > >> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> > >> >> >> 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 "Summary" 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. "Apr05") 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.
> > >> >> >>
> > >> >> >> >>
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>

That should of course be

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(A2&":"&A4)),"0000")&"'!B2")))

and in this case, you would not need all sheet names, just the start and end
names.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OCSle%23iZGHA.504@TK2MSFTNGP03.phx.gbl...
> If you change the sheet names, to say 0601, 0602, 0603, etc, you could do
it
> with
>
> =SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(H2&":"&H4)),"0000")&"'!B2")))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O2GDU5iZGHA.3524@TK2MSFTNGP04.phx.gbl...
> > 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("'"&A2:A4&"'!B2")))
> >
> > which will do Jan, Feb and Mar
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > news:efPvIwhZGHA.1200@TK2MSFTNGP03.phx.gbl...
> > > 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 "+" between each.
> > >
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:OXqrszgZGHA.1196@TK2MSFTNGP03.phx.gbl...
> > > > =INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > > news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
> > > >> 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(A2&".B2"):INDIRECT(A4&".B2"))
> > > >> where A2 contains Jan06 & 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
> > > >>
> > > >> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> > > >> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> > > >> > Try this:
> > > >> >
> > > >> > =B4+INDIRECT(A4&"!C7")
> > > >> >
> > > >> > With Apr05 in A4.
> > > >> > --
> > > >> > HTH,
> > > >> >
> > > >> > RD
> > > >> >
> > > >>
> > >
> >
>
>> -------------------------------------------------------------------------
> > -
> > > > -
> > > >> > Please keep all correspondence within the NewsGroup, so all may
> > benefit
> > > > !
> > > >>
> > >
> >
>
>> -------------------------------------------------------------------------
> > -
> > > > -
> > > >> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > >> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> > > >> >> 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
> > > >> >>
> > > >> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> > > >> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> > > >> >> > use INDIRECT
> > > >> >> >
> > > >> >> > HTH
> > > >> >> > --
> > > >> >> > AP
> > > >> >> >
> > > >> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> > > >> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> > > >> >> >> 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 "Summary" 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. "Apr05") 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.
> > > >> >> >>
> > > >> >> >> >>
> > > >> >> >
> > > >> >> >
> > > >> >>
> > > >> >>
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>
>

And of course, to kick this horse to death, you could name your ranges.

Say A1 = Jan06 is named "one"
And A1:A2 is "two"
And A1:A3 is "three"
.... etc.

Then you could use:

=SUMPRODUCT(N(INDIRECT("'"&one&"'!B2")))
=SUMPRODUCT(N(INDIRECT("'"&two&"'!B2")))
=SUMPRODUCT(N(INDIRECT("'"&three&"'!B2")))
.... etc.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eoid9BjZGHA.3532@TK2MSFTNGP05.phx.gbl...
> That should of course be
>
> =SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(A2&":"&A4)),"0000")&"'!B2")))
>
> and in this case, you would not need all sheet names, just the start and
end
> names.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OCSle%23iZGHA.504@TK2MSFTNGP03.phx.gbl...
> > If you change the sheet names, to say 0601, 0602, 0603, etc, you could
do
> it
> > with
> >
> >
=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(H2&":"&H4)),"0000")&"'!B2")))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:O2GDU5iZGHA.3524@TK2MSFTNGP04.phx.gbl...
> > > 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("'"&A2:A4&"'!B2")))
> > >
> > > which will do Jan, Feb and Mar
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > news:efPvIwhZGHA.1200@TK2MSFTNGP03.phx.gbl...
> > > > 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 "+" between each.
> > > >
> > > >
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > > news:OXqrszgZGHA.1196@TK2MSFTNGP03.phx.gbl...
> > > > > =INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
> > > > >
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > > > news:euvlUjfZGHA.1220@TK2MSFTNGP02.phx.gbl...
> > > > >> 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(A2&".B2"):INDIRECT(A4&".B2"))
> > > > >> where A2 contains Jan06 & 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
> > > > >>
> > > > >> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> > > > >> news:ehkDoVfZGHA.1192@TK2MSFTNGP04.phx.gbl...
> > > > >> > Try this:
> > > > >> >
> > > > >> > =B4+INDIRECT(A4&"!C7")
> > > > >> >
> > > > >> > With Apr05 in A4.
> > > > >> > --
> > > > >> > HTH,
> > > > >> >
> > > > >> > RD
> > > > >> >
> > > > >>
> > > >
> > >
> >
>
>> -------------------------------------------------------------------------
> > > -
> > > > > -
> > > > >> > Please keep all correspondence within the NewsGroup, so all may
> > > benefit
> > > > > !
> > > > >>
> > > >
> > >
> >
>
>> -------------------------------------------------------------------------
> > > -
> > > > > -
> > > > >> > "Bill" <bstemp@leschambres.co.za> wrote in message
> > > > >> > news:ukJ83KfZGHA.4944@TK2MSFTNGP02.phx.gbl...
> > > > >> >> 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
> > > > >> >>
> > > > >> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> > > > >> >> news:etO9uAfZGHA.1228@TK2MSFTNGP02.phx.gbl...
> > > > >> >> > use INDIRECT
> > > > >> >> >
> > > > >> >> > HTH
> > > > >> >> > --
> > > > >> >> > AP
> > > > >> >> >
> > > > >> >> > "Bill" <bstemp@leschambres.co.za> a écrit dans le message de
> > > > >> >> > news:ed2B37eZGHA.3960@TK2MSFTNGP02.phx.gbl...
> > > > >> >> >> 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 "Summary" 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. "Apr05") 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.
> > > > >> >> >>
> > > > >> >> >> >>
> > > > >> >> >
> > > > >> >> >
> > > > >> >>
> > > > >> >>
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>