Converting text to a formula

  • Thread starter Thread starter Peter McNamee
  • Start date Start date
P

Peter McNamee

I want to convert a series of concatenated text into a link formula
linking to a cell in another worksheet through its name.

an example is

cell a1 (path): c:\myfolder
cell a1 (workbook): test.xls
cell a2 (worksheet): mysheet
cell a3 (cell name): mycell

thus in cell c1 i want the formula
='c:\myfolder\[test.xls]mysheet'!mycell

i can do a similar thing if i want to use a cell reference instead of
a cell name using the INDIRECT and ADDRESS functions.

however this does not seem possible with cell names as i can't see a
way of getting their row and column.

linking to other worksheets by cell references is not very useful as
they may the cell may move.

can any one help without resorting to a too long mega formula.

i could write a macro to do it but am concenred about volatility and
generally i would be happier with a worksheet function.
 
Hi Peter

Try this formula:

="'" & path & "\[" & workbook & "]" & worksheet & "'!" & cellname

where path, workbook, worksheet and cellname are named cells

Alan
 
Hi David

Thanks for getting back.
I'm not sure your suggestion will work because I don't want to jump to
the other cell, instead, I want to return the value of whatever is in
the cell.

I looked at your web site and just above the bit about hyperlinks was
a bit about using links which suggetsed you could use the INDIRECT
function.

This worked a dream until the spreadsheet I wanted to link to was
closed. I looked in the on-line help and it said that this was the
case with links and INDIRECT unfortunately.

I am therefore still looking for a way to do this. Any ideas anyone?

Thanks

Peter

David McRitchie said:
Hi Peter,
See http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

=HYPERLINK("[c:\temp folder\xyz abc.xls]'Sheet One'!$C$5","Sheet One")
=HYPERLINK("[c:\myfolder\test.xls]'mysheet'!mycell","mysheet")

If you are using a defined name for the range use that in place of $C$5
but you can figure out the concatenation you want.



Peter McNamee said:
I want to convert a series of concatenated text into a link formula
linking to a cell in another worksheet through its name.

an example is

cell a1 (path): c:\myfolder
cell a1 (workbook): test.xls
cell a2 (worksheet): mysheet
cell a3 (cell name): mycell

thus in cell c1 i want the formula
='c:\myfolder\[test.xls]mysheet'!mycell

i can do a similar thing if i want to use a cell reference instead of
a cell name using the INDIRECT and ADDRESS functions.

however this does not seem possible with cell names as i can't see a
way of getting their row and column.

linking to other worksheets by cell references is not very useful as
they may the cell may move.

can any one help without resorting to a too long mega formula.

i could write a macro to do it but am concenred about volatility and
generally i would be happier with a worksheet function.
Click to expand...
Click to expand...
 
Hi Alan

Thanks for getting back about this. My problem is that I want to
actuall turn the concatenation into a link rather than just a piece of
text.

See my response to David Rithcie for further details.

Cheers
Peter


Hi Peter

Try this formula:

="'" & path & "\[" & workbook & "]" & worksheet & "'!" & cellname

where path, workbook, worksheet and cellname are named cells

Alan

I want to convert a series of concatenated text into a link formula
linking to a cell in another worksheet through its name.

an example is

cell a1 (path): c:\myfolder
cell a1 (workbook): test.xls
cell a2 (worksheet): mysheet
cell a3 (cell name): mycell

thus in cell c1 i want the formula
='c:\myfolder\[test.xls]mysheet'!mycell

i can do a similar thing if i want to use a cell reference instead of
a cell name using the INDIRECT and ADDRESS functions.

however this does not seem possible with cell names as i can't see a
way of getting their row and column.

linking to other worksheets by cell references is not very useful as
they may the cell may move.

can any one help without resorting to a too long mega formula.

i could write a macro to do it but am concenred about volatility and
generally i would be happier with a worksheet function.
Click to expand...
Click to expand...
 
Back
Top