Referring to other worksheets...

[xrbbaker]

I have a feeling that I’m making this overly complicated. I know I could use
a Constant, but I’m trying to avoid that.


Each month a report forecasts out 30, 60, and 90 days - Feb07, Mar07, Apr07,
etc. There are tabs for each month labeled “Feb07 Relâ€, “Mar07 Relâ€, etc.
Formulae in a forecasting/summary sheet currently reference these tabs as in:

='Feb07 Rel'!C34+'Feb07 Rel'!C37
='Mar07 Rel'!C34+'Mar07 Rel'!C37
etc.

Rather than have to edit a series of cells each month – bumping Feb07 to
Mar07 and Mar07 to Apr07, I’d like to have the formulae refer to cells on
another sheet that designate which tab is the current 30 days out tab, 60
days out tab, and 90 days out tab. That way I need only change the Referring
cell location like this:

Worksheet “Report Parmsâ€
A B
1 30 days out tab Feb07 Rel
2 60 days out tab Mar07 Rel
3 90 days out tab Apr07 Rel

My question is, how do I refer to the content of ‘Report Parms’!$B$1 in the
formula string ='Feb07 Rel'!C34+'Feb07 Rel'!C37?? I’m trying to concatenate
‘Report Parms’!$B$1&C34 but I can’t figure out how to lay out the quotes, or
if that doesn’t work, what else is needed.

Thanks for any help.

Russ

 

[JE McGimpsey]

Take a look at the INDIRECT() function. For example:

=INDIRECT("'" & B1 & "'!C34")

 

[xrbbaker]

Much obliged!! I was screwing around with the Indirect function. At least I
was in the right area... I couldn't figure out the proper quoting nesting of
" ' " until your solution. Thanks much!!

Russ