converting formula to text

[jsuden]

I have a formula in column B and then column C has another formula
which references column B, so for example.

Column B
VLOOKUP(A4,'sheet2'!$A$1:$B$92,2,FALSE)

Column C
INDEX('N:\INVEST\sheet1'!Prices.B1.ROR,MATCH(B$3,
'N:\INVEST\sheet2.xls'!Prices.Date))/100

If the Vlookup in column B were to give the result XY, I would want the
B1 in 'N:\INVEST\sheet1'!Prices.B1.ROR to actually say XY, as opposed
to saying B1.
I want to stay away from copy-paste special since the v-lookups will
always be giving different results...
Is there any way to do this?

 

[Guest]

You can try using Indirect...

INDEX(Indirect("'N:\INVEST\sheet1'!Prices." & B1 & ".ROR"),MATCH(B$3,
'N:\INVEST\sheet2.xls'!Prices.Date))/100

 

[Guest]

You could try Indirect, but as stated in help, Indirect doesn't work with
references to closed workbooks (the case here), so you would be wasting your
time as they say. <g>

 

[Guest]

And you learn something new every day. I wondered that (will it work with a
closed workbook) after I posted. You posted before I had the chance to even
look it up...

As always. Thanks Tom...

 

[jsuden]

Thanks for your help...So I assume there is no way to do this then?

 

[Dave Peterson]

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.