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.

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.

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.

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.