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 