If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need help creating formula based on cell value
Hello,
Is it possible to create a formula using a cell value to create a formula. basically I have multiple sheets with the name as a month. I have one sheet that does my reporting. So Instead of having a to create a seperate formula in each cell to reference each month (ie. ='jan report'!$A$14, ='feb report'!$A$14, etc) I want to be able to do something like create a formula to reference a cell that would contain the month name. For example. A1 b1 Jan Feb a2 ="'" & A1 & " Report'!" & $A$14 b2 ="'" & A1 & " Report'!" & $A$14 The goal would be a formula in A2 that would be ='jan report'!$A$14 and the formula in b2 would be ='Feb report'!$A$14 thus each formual resulting in a value that came from the appropriate cell in the corresponding sheet. I can not get this to work. Hopefully this makes sense and someone can help. Thanks. |
#2
|
|||
|
|||
Use your text formula inside the INDIRECT() function, i.e.,
=INDIRECT("'" & A1 & " Report'!" & $A$14) Duke "Brad" wrote: Hello, Is it possible to create a formula using a cell value to create a formula. basically I have multiple sheets with the name as a month. I have one sheet that does my reporting. So Instead of having a to create a seperate formula in each cell to reference each month (ie. ='jan report'!$A$14, ='feb report'!$A$14, etc) I want to be able to do something like create a formula to reference a cell that would contain the month name. For example. A1 b1 Jan Feb a2 ="'" & A1 & " Report'!" & $A$14 b2 ="'" & A1 & " Report'!" & $A$14 The goal would be a formula in A2 that would be ='jan report'!$A$14 and the formula in b2 would be ='Feb report'!$A$14 thus each formual resulting in a value that came from the appropriate cell in the corresponding sheet. I can not get this to work. Hopefully this makes sense and someone can help. Thanks. |
#3
|
|||
|
|||
Duke,
It comes back as #REF. Evaluating the formula shows that it is creating the Jan Report properly, but the $A$14 is failing. Any ideas? Thanks. "Duke Carey" wrote: Use your text formula inside the INDIRECT() function, i.e., =INDIRECT("'" & A1 & " Report'!" & $A$14) Duke "Brad" wrote: Hello, Is it possible to create a formula using a cell value to create a formula. basically I have multiple sheets with the name as a month. I have one sheet that does my reporting. So Instead of having a to create a seperate formula in each cell to reference each month (ie. ='jan report'!$A$14, ='feb report'!$A$14, etc) I want to be able to do something like create a formula to reference a cell that would contain the month name. For example. A1 b1 Jan Feb a2 ="'" & A1 & " Report'!" & $A$14 b2 ="'" & A1 & " Report'!" & $A$14 The goal would be a formula in A2 that would be ='jan report'!$A$14 and the formula in b2 would be ='Feb report'!$A$14 thus each formual resulting in a value that came from the appropriate cell in the corresponding sheet. I can not get this to work. Hopefully this makes sense and someone can help. Thanks. |
#4
|
|||
|
|||
My error. Try
=INDIRECT("'" & A1 & " Report'! & $A$14") "Brad" wrote: Duke, It comes back as #REF. Evaluating the formula shows that it is creating the Jan Report properly, but the $A$14 is failing. Any ideas? Thanks. "Duke Carey" wrote: Use your text formula inside the INDIRECT() function, i.e., =INDIRECT("'" & A1 & " Report'!" & $A$14) Duke "Brad" wrote: Hello, Is it possible to create a formula using a cell value to create a formula. basically I have multiple sheets with the name as a month. I have one sheet that does my reporting. So Instead of having a to create a seperate formula in each cell to reference each month (ie. ='jan report'!$A$14, ='feb report'!$A$14, etc) I want to be able to do something like create a formula to reference a cell that would contain the month name. For example. A1 b1 Jan Feb a2 ="'" & A1 & " Report'!" & $A$14 b2 ="'" & A1 & " Report'!" & $A$14 The goal would be a formula in A2 that would be ='jan report'!$A$14 and the formula in b2 would be ='Feb report'!$A$14 thus each formual resulting in a value that came from the appropriate cell in the corresponding sheet. I can not get this to work. Hopefully this makes sense and someone can help. Thanks. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cell shows formula and not the result of the formula. | stumpy1220 | Worksheet Functions | 2 | January 14th, 2005 06:11 PM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
copying and controling a formula cell to cell | kevin | Worksheet Functions | 5 | February 13th, 2004 02:26 PM |
Help with CELL Formula (Troubleshooting) | Jeff J | Worksheet Functions | 4 | December 15th, 2003 05:11 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |