A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

need help creating formula based on cell value



 
 
Thread Tools Display Modes
  #1  
Old April 1st, 2005, 05:27 PM
Brad
external usenet poster
 
Posts: n/a
Default 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  
Old April 1st, 2005, 06:45 PM
Duke Carey
external usenet poster
 
Posts: n/a
Default

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  
Old April 1st, 2005, 06:59 PM
Brad
external usenet poster
 
Posts: n/a
Default

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  
Old April 1st, 2005, 07:51 PM
Duke Carey
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 01:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.