convert text to formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5

when I link to this (=A1) it comes up as text instead of aplying the formula the other cell created.

B asicly what I'm doing is makeing a cell that contains a file name (the file.xls) and telling the rest of my sheet to get information off of the file given in the cell.
 
Check to see if you have formatted as text, if so format as general and
double click the cell, then enter
if you get ='[the file.xls]Sheet1'!$A$5:$G$5 as text string then you have to
check the source cell instead

--

Regards,

Peo Sjoblom

ouch said:
I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5

when I link to this (=A1) it comes up as text instead of aplying the
Click to expand...
formula the other cell created.
B asicly what I'm doing is makeing a cell that contains a file name (the
Click to expand...
file.xls) and telling the rest of my sheet to get information off of the
file given in the cell.
 
You need to use the INDIRECT function to turn a text string into a reference
that excel recognises, eg

=INDIRECT(A1)

BUT!!!!!!

it will not work on a closed workbook.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all :-)
----------------------------------------------------------------------------



ouch said:
I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5

when I link to this (=A1) it comes up as text instead of aplying the formula the other cell created.

B asicly what I'm doing is makeing a cell that contains a file name (the
Click to expand...
file.xls) and telling the rest of my sheet to get information off of the file
given in the cell.
 
It looks like you could use the INDIRECT function. However the other file has to be open for this function to work.

Good luck,
Mark Graesser
(e-mail address removed)

----- ouch wrote: -----

I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5

when I link to this (=A1) it comes up as text instead of aplying the formula the other cell created.

B asicly what I'm doing is makeing a cell that contains a file name (the file.xls) and telling the rest of my sheet to get information off of the file given in the cell.
 
inderect seems to only work for numbers :(

----- Mark Graesser wrote: -----

It looks like you could use the INDIRECT function. However the other file has to be open for this function to work.

Good luck,
Mark Graesser
(e-mail address removed)

----- ouch wrote: -----

I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5

when I link to this (=A1) it comes up as text instead of aplying the formula the other cell created.

B asicly what I'm doing is makeing a cell that contains a file name (the file.xls) and telling the rest of my sheet to get information off of the file given in the cell.
 
both cells are formatted as general, the other cell (A1) is doing what it's supposed to. it's using CONCATENATE to generate said information. combining everything to a string which I thought could be used to create a formula on the fly. maybe you can't do this with excel?

----- Peo Sjoblom wrote: -----

Check to see if you have formatted as text, if so format as general and
double click the cell, then enter
if you get ='[the file.xls]Sheet1'!$A$5:$G$5 as text string then you have to
check the source cell instead

--

Regards,

Peo Sjoblom

ouch said:
I have a cell which produces file links in excel however the results are text.
='[the file.xls]Sheet1'!$A$5:$G$5
when I link to this (=A1) it comes up as text instead of aplying the formula the other cell created.
B asicly what I'm doing is makeing a cell that contains a file name (the
Click to expand...
Click to expand...
file.xls) and telling the rest of my sheet to get information off of the
file given in the cell.
 
Not really, you would need VBA for that if you mean turning a string that
looks like
='[the file.xls]Sheet1'!$A$5:$G$5
if you exclude the equal sign you can use indirect, assume the string looks
like

If you concatenate looks like

=CONCATENATE("'[",E3,"]",F3,"'!",G3)

where E3 holds the file name, F3 holds the sheet name and G3 the range,

then to sum this range you can use

=SUM(INDIRECT(A1))

or directly

=SUM(INDIRECT(CONCATENATE("'[",E3,"]",F3,"'!",G3)))

you cannot include the equal sign

Note that the other workbook has to be opened


--

Regards,

Peo Sjoblom

ouch said:
both cells are formatted as general, the other cell (A1) is doing what
Click to expand...
it's supposed to. it's using CONCATENATE to generate said information.
combining everything to a string which I thought could be used to create a
formula on the fly. maybe you can't do this with excel?
----- Peo Sjoblom wrote: -----

Check to see if you have formatted as text, if so format as general and
double click the cell, then enter
if you get ='[the file.xls]Sheet1'!$A$5:$G$5 as text string then you have to
check the source cell instead

--

Regards,

Peo Sjoblom

ouch said:
I have a cell which produces file links in excel however the
Click to expand...
results are
text.
='[the file.xls]Sheet1'!$A$5:$G$5
when I link to this (=A1) it comes up as text instead of aplying
Click to expand...
Click to expand...
the
formula the other cell created. name (the
file.xls) and telling the rest of my sheet to get information off of the
file given in the cell.
Click to expand...
 
Like Peo said - Don't include the = sign. It does work, so if you still get a
proiblem, then the reference can't be 100% correct.
 
combining all of the functions into one cell fixed the problem. (it's like 3 lines long though :) )
 
i've got the same problem and indirect has solved it.

however, i still want to know how to convert text strings to formulas.

ex. a1:= a2:sum( a3:b1:b5 a4:)
and then concatenate(a1:a4) would result in =sum(b1:b5)

however it doesnt evaluate the formula and displays the formula as tex
instead.

the only way i found to force it to evaluate the formula is wheni cop
pasted special values and then clicked on the formula and then presse
enter.

is there an easier way
 
Hi unfortunately (without VBA) there is no easier way. Excel only
allows conervting references (via INDIRECT)
Frank
 
Back
Top