First, I'd drop the =concatenate() function and just use the concatenate
operator (&).

="'" & path & jobnumber...

Second, the function you'd want to use that's built into excel is =indirect().
But that function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

Third, since you posted this in the .programming newsgroup, maybe you're doing
that concatenation in code and populating the cell with a formula that retrieves
the value???

If that third thing is true, then you may want to share the code--this technique
should work ok.



Lynn wrote:

This is related to my post on 5/8. Searched but didn't find answer to this
new problem... I need to pull data from workbook A into workbook B. Workbook
A varies and is specified by user input of variable 'jobnumber'. Most of the
time I will need to add together the data from multiple cells in A and put
this value in the cell in B. Each target cell in B uses different source
cells, so I need to code this in every cell, not just one value that I
calculate and shove in lots of places. I will derive the path/link to the
source by concatenating a static path, the variable specified by the user,
and the specific worksheet and cell in A. I then need to add together the
values of the source cells referenced by the concatenated path, and put the
result in the destination cell. Concatenation gives me the path as a text
string. What I need is the value contained in the cell referenced by this
string, so I can add it to other values. I've tried various commands but
either haven't found the right one to accomplish this or am using it
incorrectly. I have =concatenate("'", path, jobnumber,
"\[workbookname.xls]worksheet'!a1"), where path is \\srvr\abc\def\ghi\. Where
do I need to go from here? Thanks...

--

Dave Peterson