referencing the content rather than the result of a cell's formula

[Guest]

I am trying to reference the content rather than the result of a cell's formula.

Example:
In cell A7 I have the formula "='Income Tracking'!C35" which returns the value "EED".

In cell B7, I have a more complex formula in which I also need to reference the cell 'Income Tracking'!C35, but I would like to do this by referencing cell A7.

Is there a way to do this? I tried INDIRECT, but got a #REF! return. Thanks. Lisa

 

[Frank Kabel]

Hi
why not simply use A7 as a reference in your formula. e.g. enter the
following in B7:
=VLOOKUP(A7,range,2,0)

 

[Guest]

thanks for the reply. I tried it, but I get the result #NAME?. Any other thoughts?

 

[Frank Kabel]

Hi
I just gave you an example how to use A7 in a more complex formula as
you didn't provide your formula from B7
My example won't work as it has a 'dummy' second parameter named
'range'. You may post your formula from B7 and we can show you how to
insert a reference to cell A7

 

[Guest]

OK - here is the formula I want in cell B7-

=OFFSET(X,-1,0

where X is completed by referencing the content (not value) of cell A7, and thus equals 'Income Tracking'!C3

Thanks so much. Lisa

 

[Frank Kabel]

Hi
if you have entered 'Income Tracking'!C35 as text in A7 (or it is the
result of a formula) you can use:
=OFFSET(INDIRECT(A7),-1,0)

But if A7 has the formula:
='Income Tracking'!C35
no way to reference this range without using a UDF (which returns the
formula of A7)