Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
A cell reference in a formula changing | Excel Worksheet Functions | |||
Need formula for sheet & cell reference | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |