| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting formula to text
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
converting formula to text
You can try using Indirect...
INDEX(Indirect("'N:\INVEST\sheet1'!Prices." & B1 & ".ROR"),MATCH(B$3, 'N:\INVEST\sheet2.xls'!Prices.Date))/100 -- HTH... Jim Thomlinson " wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
converting formula to text
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 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: You can try using Indirect... INDEX(Indirect("'N:\INVEST\sheet1'!Prices." & B1 & ".ROR"),MATCH(B$3, 'N:\INVEST\sheet2.xls'!Prices.Date))/100 -- HTH... Jim Thomlinson " wrote: 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
converting formula to text
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... -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: You can try using Indirect... INDEX(Indirect("'N:\INVEST\sheet1'!Prices." & B1 & ".ROR"),MATCH(B$3, 'N:\INVEST\sheet2.xls'!Prices.Date))/100 -- HTH... Jim Thomlinson " wrote: 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
converting formula to text
Thanks for your help...So I assume there is no way to do this then?
Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: You can try using Indirect... INDEX(Indirect("'N:\INVEST\sheet1'!Prices." & B1 & ".ROR"),MATCH(B$3, 'N:\INVEST\sheet2.xls'!Prices.Date))/100 -- HTH... Jim Thomlinson " wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting calculation expressed as text into a formula | Excel Discussion (Misc queries) | |||
| converting results from formula to text in a new column | New Users to Excel | |||
| Converting text string back into a formula | Excel Discussion (Misc queries) | |||
| Converting concatenation formula to text | Excel Programming | |||
| converting constant/text to formula and evaluate it | Excel Programming | |||
Hybrid Mode
