assemble link from different cells and return value

[tastic96]

Hey there, this is my first time on a forum....

I need to link from a worksheet to another file. Instead of creating
this link normally (just by opening the other file and selecting the
desired cell), I am trying to assemble the link from a number of
different cells in different worksheets...

for example.

Cell A1 in worksheet1 = long file path

something like - file://nuefil08\nuefeb4\Work\Data
Entry\31.03.05\EM22.xls

Cell A1 in worksheet2 = worksheet name and cell reference

something like - Monthy Totals Line 1!C$159

The link should be the sum of the 2 cells, however whatever I have
tried, excel only returns the text string, not the actual value of the
cell from the link...

I've been trying everything for 2 days and it's starting to do my head
in... Any help would be appreciated! Cheers...

 

[Dave Peterson]

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

Look for pull.zip

 

[Guest]

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.