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 fo
each month/year. Onthe summary sheet I use column A for the month/yea
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 rang
on each sheet but I would like the formula to take the sheet name fro
Columa A rather than hard coding it.

So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take th
"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

 

[Guest]

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

 

[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

 

[Guest]

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.

 

[Biff]

Hi!

Are you using the R1C1 reference style?

Assume R1C1 (A1) = Quality

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

Evaluates to:

=Quality!B2 (R2C2)

Biff

 

[Guest]

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.