convert string to value and sum

[Lynn]

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]

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.

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...

 

[Lynn]

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).

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.

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...