Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using a worksheet name in a formula
Hi,
I have 100+ worksheets. I have formulas that hard code those worksheet names. Such as the the simple formula: =Sheet58!$E$2 I would like the "58" to be generated from cell (i.e. A1 has a value of 58) so I can have a formula that like: ="Sheet($A$1)"!$E$2 What is the correct syntax? I tried INDIRECT, but could not get it to work ... |
#2
|
|||
|
|||
Hi Kevin,
INDIRECT Worksheet Function http://www.mvps.org/dmcritchie/excel/indirect.htm and http://www.mvps.org/dmcritchie/excel/buildtoc2.htm =INDIRECT($A$1 & "!$E$2) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KevinB" wrote in message ... Hi, I have 100+ worksheets. I have formulas that hard code those worksheet names. Such as the the simple formula: =Sheet58!$E$2 I would like the "58" to be generated from cell (i.e. A1 has a value of 58) so I can have a formula that like: ="Sheet($A$1)"!$E$2 What is the correct syntax? I tried INDIRECT, but could not get it to work .. |
#3
|
|||
|
|||
Hi Kevin,
Try: =INDIRECT("Sheet"&$A$1&"!$E$2") "KevinB" wrote: Hi, I have 100+ worksheets. I have formulas that hard code those worksheet names. Such as the the simple formula: =Sheet58!$E$2 I would like the "58" to be generated from cell (i.e. A1 has a value of 58) so I can have a formula that like: ="Sheet($A$1)"!$E$2 What is the correct syntax? I tried INDIRECT, but could not get it to work .. |
#4
|
|||
|
|||
Cool, that worked! Thx!
Now, my next issue. The formula you provided; =INDIRECT("Sheet"&$A$61&"!$C$2") still has the cell reference "A61" so I have to hard code the "61." Can I reference the "A" column for the current row? Therefore, I don't have to type the row number? "David McRitchie" wrote: Hi Kevin, INDIRECT Worksheet Function http://www.mvps.org/dmcritchie/excel/indirect.htm and http://www.mvps.org/dmcritchie/excel/buildtoc2.htm =INDIRECT($A$1 & "!$E$2) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KevinB" wrote in message ... Hi, I have 100+ worksheets. I have formulas that hard code those worksheet names. Such as the the simple formula: =Sheet58!$E$2 I would like the "58" to be generated from cell (i.e. A1 has a value of 58) so I can have a formula that like: ="Sheet($A$1)"!$E$2 What is the correct syntax? I tried INDIRECT, but could not get it to work .. |
#5
|
|||
|
|||
Hi Kevin,
=ROW() will provide the cell's row number =COLUMN() will provide the cell's column number "KevinB" wrote ... Cool, that worked! Thx! Now, my next issue. The formula you provided; =INDIRECT("Sheet"&$A$61&"!$C$2") |
#6
|
|||
|
|||
Hi David, Thx for your patience . . . .
I did not state my question clearly. In the following formula, =INDIRECT("Sheet"&$A$61&"!$C$2") I want to replace the $A$61 with value in the A column for that row. Is there a function that will return the value of a specific cell on the current row? Example: Cell A61 contains the value 53. I want the INDIRECT function to lookup the "Sheet53" without having to put $A$61 in the formula. Thx! Kevin "David McRitchie" wrote: Hi Kevin, =ROW() will provide the cell's row number =COLUMN() will provide the cell's column number "KevinB" wrote ... Cool, that worked! Thx! Now, my next issue. The formula you provided; =INDIRECT("Sheet"&$A$61&"!$C$2") |
#7
|
|||
|
|||
Hi Kevin,
Your goal is to produce a string that looks like sheet53!$C$2 and to place that formula adjusted to it's new location as the argument of INDIRECT. cell A61: 53 test G61: ="'sheet" & OFFSET(G61,0,1-COLUMN()) & "'!$C$2" cell H61: =INDIRECT("'sheet" & OFFSET(H61,0,1-COLUMN()) & "'!$C$2" ) The G61 and H61 internal are the address of the cell itself --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David McRitchie" wrote in message ... Hi Kevin, =ROW() will provide the cell's row number =COLUMN() will provide the cell's column number "KevinB" wrote ... Cool, that worked! Thx! Now, my next issue. The formula you provided; =INDIRECT("Sheet"&$A$61&"!$C$2") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
If Formula | Excel Discussion (Misc queries) | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
Worksheet name and Backward compatibility | Excel Discussion (Misc queries) | |||
Display excel formula on worksheet | Excel Worksheet Functions |