Thanks Dave. To answer some questions: I did try the indirect function, even
with the other workbook open, and I still got an error. I'll try using the &
operator and let you know what happens. Will also check out the link, thanks
for that.
I'm not doing it in code, at least not yet. I just posted here hoping more
ideas would be available. Wasn't sure which group this would be best posted
in, so opted for here. I suspect doing it in code would involve even more
work at this point, creating a separate list or table indicating the source
cells for each target cell since I can't modify the existing target
spreadsheet. Have done other programming in a previous lifetime, but not much
VBA at all, think that would be more work for me than just editing what the
author already has in those cells (which are hard coded to the original
source spreadsheet, I've got the task of making it converting it).
"Dave Peterson" wrote:
> 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
>
Similar Threads
