Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 1st 05, 05:27 PM
Brad
 
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   Report Post  
Old April 1st 05, 06:45 PM
Duke Carey
 
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   Report Post  
Old April 1st 05, 06:59 PM
Brad
 
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   Report Post  
Old April 1st 05, 07:51 PM
Duke Carey
 
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with creating Formula again Benny Excel Worksheet Functions 13 March 20th 05 04:59 PM
changing value of a cell based on another cell color Gary Excel Discussion (Misc queries) 2 January 30th 05 10:19 AM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM
How do I do conditional format based on a cell with a formula? Justin Excel Worksheet Functions 9 December 17th 04 02:05 PM
Conditional Formatting for dates spacerocket Excel Worksheet Functions 2 November 4th 04 10:13 AM


All times are GMT +1. The time now is 04:33 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017