use a formula as a cell reference in a function
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 |
Hi Tony,
Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "tony h" wrote in message ... 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 |
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 |
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 |
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 |
Hi!
Are you using the R1C1 reference style? Assume R1C1 (A1) = Quality =INDIRECT(R1C1&"!R2C2",FALSE) Evaluates to: =Quality!B2 (R2C2) Biff "Micah" wrote in message ... 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 |
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" wrote in message ... 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 |
All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com