use a formula as a cell reference in a function

[tony h]

I have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.

in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.

So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3

Many thanks,

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[tony h]

Thank you for your help.

A bit of playing about (and the function you so kindly pointed out) gives =INDIRECT("'" & TEXT(A4,"mmm yy") & "'!$C$3",TRUE)

without the TEXT function it brought in the date serial number, and the sum function is : =SUM(INDIRECT("'"&TEXT(A4,"mmm yy")&"'!$C$6:C40",TRUE))

Many thanks

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[bj]

A little awkward but try
=sum(offset(indirect("'"&A3&"'!$C$6"),0,0,42)

"tony h" wrote:

>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Niek Otten]

Hi Tony,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook with a Summary sheet and then a series of sheets for
> each month/year. Onthe summary sheet I use column A for the month/year
> as a date formatted "mmm yy" ie the same as the sheet names.
>
> in columns B,C,D I want formulae that sums or averages a fixed range
> on each sheet but I would like the formula to take the sheet name from
> Columa A rather than hard coding it.
>
> So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the
> "Dec 04" from the text in cell A3
>
> Many thanks,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>

[Micah]

I have the same question, but instead of "mmm yy" I want to referene a cell
with the word "Quality" in it. I then want to concatenate that cell with
R2C2 such that I get a formula =Quality!R2C2.

I tried the formula below with the cell for Quality in the text function
with no formating after the , i.e. text(r1c1, ) and could not get it to work.

Your help would be appreciated.

"tony h" wrote:

>
> Thank you for your help.
>
> A bit of playing about (and the function you so kindly pointed out)
> gives =INDIRECT("'" & TEXT(A4,"mmm yy") & "'!$C$3",TRUE)
>
> without the TEXT function it brought in the date serial number, and the
> sum function is : =SUM(INDIRECT("'"&TEXT(A4,"mmm
> yy")&"'!$C$6:C40",TRUE))
>
> Many thanks
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=392470
>
>

[Biff]

Hi!

Are you using the R1C1 reference style?

Assume R1C1 (A1) = Quality

=INDIRECT(R1C1&"!R2C2",FALSE)

Evaluates to:

=Quality!B2 (R2C2)

Biff

"Micah" <[email protected]> wrote in message
news:[email protected]...
>
> I have the same question, but instead of "mmm yy" I want to referene a
> cell
> with the word "Quality" in it. I then want to concatenate that cell with
> R2C2 such that I get a formula =Quality!R2C2.
>
> I tried the formula below with the cell for Quality in the text function
> with no formating after the , i.e. text(r1c1, ) and could not get it to
> work.
>
> Your help would be appreciated.
>
> "tony h" wrote:
>
>>
>> Thank you for your help.
>>
>> A bit of playing about (and the function you so kindly pointed out)
>> gives =INDIRECT("'" & TEXT(A4,"mmm yy") & "'!$C$3",TRUE)
>>
>> without the TEXT function it brought in the date serial number, and the
>> sum function is : =SUM(INDIRECT("'"&TEXT(A4,"mmm
>> yy")&"'!$C$6:C40",TRUE))
>>
>> Many thanks
>>
>>
>> --
>> tony h
>> ------------------------------------------------------------------------
>> tony h's Profile:
>> http://www.excelforum.com/member.php...o&userid=21074
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=392470
>>
>>

[Micah]

Thanks Biff, I got it to work now. I have been looking for the function in
excel which acts like formula in VBA for a long time. Thanks again.

"Biff" wrote:

> Hi!
>
> Are you using the R1C1 reference style?
>
> Assume R1C1 (A1) = Quality
>
> =INDIRECT(R1C1&"!R2C2",FALSE)
>
> Evaluates to:
>
> =Quality!B2 (R2C2)
>
> Biff
>
> "Micah" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I have the same question, but instead of "mmm yy" I want to referene a
> > cell
> > with the word "Quality" in it. I then want to concatenate that cell with
> > R2C2 such that I get a formula =Quality!R2C2.
> >
> > I tried the formula below with the cell for Quality in the text function
> > with no formating after the , i.e. text(r1c1, ) and could not get it to
> > work.
> >
> > Your help would be appreciated.
> >
> > "tony h" wrote:
> >
> >>
> >> Thank you for your help.
> >>
> >> A bit of playing about (and the function you so kindly pointed out)
> >> gives =INDIRECT("'" & TEXT(A4,"mmm yy") & "'!$C$3",TRUE)
> >>
> >> without the TEXT function it brought in the date serial number, and the
> >> sum function is : =SUM(INDIRECT("'"&TEXT(A4,"mmm
> >> yy")&"'!$C$6:C40",TRUE))
> >>
> >> Many thanks
> >>
> >>
> >> --
> >> tony h
> >> ------------------------------------------------------------------------
> >> tony h's Profile:
> >> http://www.excelforum.com/member.php...o&userid=21074
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=392470
> >>
> >>
>
>
>