Home |
Search |
Today's Posts |
#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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with creating Formula again | Excel Worksheet Functions | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
How do I do conditional format based on a cell with a formula? | Excel Worksheet Functions | |||
Conditional Formatting for dates | Excel Worksheet Functions |