Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert string to value and sum
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert string to value and sum
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert string to value and sum
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert to string help | Excel Discussion (Misc queries) | |||
Convert string | Excel Programming | |||
Convert string from one codepage to another | Excel Programming | |||
How to convert string to date | Excel Worksheet Functions | |||
convert a string to range? | Excel Worksheet Functions |