+ Reply to Thread
Results 1 to 12 of 12

How do I subsitute part of a formula linked to another workbook?

  1. #1
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10

    How do I subsitute part of a formula linked to another workbook?

    Hi folks I hope someone has experience on this particular topic....

    I've run into a roadblock in excel 2003 in trying to create an accounting spreadsheet that will pull in information from several separate workbooks (invoice files) using formulas which substitute invoice Nos. (which coincide with the tab/worksheet names). My obstacle has something to do with order of operation/calculation I presume... I've successfully used labels to bring in information from separate tabs on the same file by using the substitute function with generic formulas.... in this case exel processes the formula, likely because it calculates the linked information first in its order of calculation; then trys to substitute information into the result of a calculation already made....

    I'm trying to find a way to tell excel to look at respective cells in the same file but on a different tabs/sheets...

    For example; I've been trying to pull in the date an invoice was issued for client "X". I plug in a formula in a remote cell of my accounting spreadsheet "Y" which basically says to bring in the value on file ["X"]; worksheet "tab"; cell $J$11 - where "tab is just a filler that I would like to use the substitute function to exchange with respective invoice Nos. (i.e. 049; 050; 051; etc.) My hope is that by adding these 3-digit sheet identifiers; all other information from respective cells (in file "X") will be pulled into my accounting sheet - file "Y" (i.e. invoice total; applicable pst; gst; expenses; etc.) I have written as many formulas as required for the different types of info sought after, the only variable being the 3-digit worksheet identifier. The problem has been that excel first processes the formula in the remote formula cell(in file "Y"); then applies the substitute function to info already brought in from File "X"; whether it is a date value; or a currency value. I can't figure how to have the substitute function change the formula... not the result of the original function...!

    I've tried to format the source formula cell as text (therefore not calculating a result at that instant) however the substitute function inherits the format of the source formula cell. Does anyone have any experience with this problem... any help will be greatly appreciated. Thanks.

    Regards,

    Sheldon

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,473
    I think the function you are looking for is the INDIRECT function rather than the SUBSTITUTE function.

    Just recognize that the INDIRECT function can only work on open workbooks. If file(x) is closed, you'll get an error.

    =INDIRECT("["&A1&"]"&A2&"!j11") (or something like that).

  3. #3
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10
    Quote Originally Posted by MrShorty
    I think the function you are looking for is the INDIRECT function rather than the SUBSTITUTE function.

    Just recognize that the INDIRECT function can only work on open workbooks. If file(x) is closed, you'll get an error.

    =INDIRECT("["&A1&"]"&A2&"!j11") (or something like that).
    Thanks for your response Mr. Shorty. I've been trying to include the indirect function but will revisit how I'm placing it in the equation (I was approaching as a sub-function within the substitute equation... Just in case I didn't communicate clearly previously, my approach has been to have the "&A2&" part be substituted for the various tabs/spreadsheets which represent invoice Nos. So for example if your formula was my remote source formula (located in cell D15); and if N14 is where I enter in the appropriate invoice Tag ("049" for example), I have been entering in each result cell something like this:
    =IF(N$14=0," ",(SUBSTITUTE($D15,"&A2&",N$14)))... I actually started out using the indirect function to setup the last N$14... Hope I'm not being too confusing....

    Please let me know if I'm on the right track...

    Sheldon

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,473
    Either I'm completely misunderstanding what you want to do, or your on the wrong track.

    The substitute function works on text strings, not formulas. The text string could be the result of a formula, but the substitute function would operate on the resulting text string not the formula.

    The indirect function takes a text string and attempts to return a cell reference. This is what I understand you are trying to do.

    Conceivably, the substitute function could be used as part of building the text string used by the indirect function. Example (comma delimits cells)

    formulas
    '[book1.xls]sheet1!A1,=indirect(a1)
    =substitute(a1,"t1","t2"),=indirect(a2)

    result
    [book1.xls]sheet1!a1,(value in book1.xls->sheet1->cellA1)
    [book1.xls]sheet2!a1,(value in book1.xls->sheet2->cellA1)

  5. #5
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10
    Thanks for your response Mr. Shorty...

    I think your resulting line is exactly what I'm hoping to achieve by just adding the sheet name in the top row of a column with the following cells pulling in respective information from the specifed sheet based on imbedded formulas.... I will just spend a little time now digesting your suggested formulas and try to adapt it to my application... Thanks I will let you know how it turns out later today.

    Regards,

    Sheldon

  6. #6
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10
    Quote Originally Posted by MrShorty
    Either I'm completely misunderstanding what you want to do, or your on the wrong track.

    The substitute function works on text strings, not formulas. The text string could be the result of a formula, but the substitute function would operate on the resulting text string not the formula.

    The indirect function takes a text string and attempts to return a cell reference. This is what I understand you are trying to do.

    Conceivably, the substitute function could be used as part of building the text string used by the indirect function. Example (comma delimits cells)

    formulas
    '[book1.xls]sheet1!A1,=indirect(a1)
    =substitute(a1,"t1","t2"),=indirect(a2)

    result
    [book1.xls]sheet1!a1,(value in book1.xls->sheet1->cellA1)
    [book1.xls]sheet2!a1,(value in book1.xls->sheet2->cellA1)
    Sorry Mr. Shorty - I have to ask for a little clarification with the above formulas... I'm not sure where the "a2" fits in to result in finding cell A1 on sheet2...? Is "=substitute(a1,"t1","t2"),=indirect(a2)" a single actual forumla which is asking for the result of the formula in cell a1 substituting text "t2" in for text "t1"...? and why is the formula followed by or ending with ",=indirect(a2).

    I don't mean to sound completely lost Mr. Shorty, but unfortunately I really am at this level of excel.... I was extactic to see that you have correctly indicated the result I'm trying to achieve; however I don't understand exactly how you got there as I'm probably mis-interpretting your formulas.... I've assumed that you have indicated two separate formulas, but am not sure if the ending parts are part of the actual formulas or part of your explaing their respective identities (to me)..... I think we're on the right track though.... just a little more help pleaseeee...

    very appreciatively,

    Sheldon

  7. #7
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302

    A function to locate values in other workbooks

    The following user defined function may help you

    ==========

    '---------
    ' Usage in cell: =gData("Book2.xls", "sheet2", C7)
    ' ........................... string ...... string . . . cell-reference
    ' Returns the value in Book2.xls, sheet2, cell C7
    ' ie the value of cell with hard address [Book2.xls]sheet2!C7
    '
    '
    Function gData(wb As String, ws As String, datacel As Range) As Variant
    gData = Workbooks(wb).Sheets(ws).Range(datacel.Address)
    End Function

    ==========


    How to install this user defined function
    - Open your workbook
    - Record a small macro
    - Find the macro and edit it
    - Replace all of the text of that macro with the above text that is
    between but not including the ========== lines
    - You can then use the function in a workbook cell

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,473
    Quote Originally Posted by Scej12
    Sorry Mr. Shorty - I have to ask for a little clarification with the above formulas... I'm not sure where the "a2" fits in to result in finding cell A1 on sheet2...? Is "=substitute(a1,"t1","t2"),=indirect(a2)" a single actual forumla which is asking for the result of the formula in cell a1 substituting text "t2" in for text "t1"...? and why is the formula followed by or ending with ",=indirect(a2).

    I don't mean to sound completely lost Mr. Shorty, but unfortunately I really am at this level of excel.... I was extactic to see that you have correctly indicated the result I'm trying to achieve; however I don't understand exactly how you got there as I'm probably mis-interpretting your formulas.... I've assumed that you have indicated two separate formulas, but am not sure if the ending parts are part of the actual formulas or part of your explaing their respective identities (to me)..... I think we're on the right track though.... just a little more help pleaseeee...

    very appreciatively,

    Sheldon
    I guess I didn't communicate it clearly enough. the commas between the functions were meant to separate individual cells. So the string/substitute functions are in column A and the indirect functions/results are in column B. I assumed starting at row 1.

    Does that clarify?

  9. #9
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10

    Thanks!!!!

    Quote Originally Posted by MrShorty
    I guess I didn't communicate it clearly enough. the commas between the functions were meant to separate individual cells. So the string/substitute functions are in column A and the indirect functions/results are in column B. I assumed starting at row 1.

    Does that clarify?
    Success!!!

    Mr. Shorty - Thanks a million.... When I finally understood what you tried to convey initially (BTW you did indicate that commas separated cells, its just that I'm brave enough to seek a solution outside of my forte but still somewhat out of my league when it comes to understanding abbreviated conversation with excel).... Anyway I was able to apply your suggested formula with one small catch which I made due to pure fluke.... I had to prefix the external reference with an extra apostrophy (') in order for my source cell since the single (') was automatically dropped. With this small addition I was able to switch between worksheets on the same file as you predicted.

    I went one step further in order to limit the number of required cells by combining the substitute and indirect functions in the final result cell. Therefore I came up with the following formula which seems to be working so far:

    =IF(N$14=0," ",INDIRECT(SUBSTITUTE($D15,"TAB",N$14)))

    where:
    - the sheet name/invoice no. is entered into cell n14;
    - the source formula you gave was adapted and entered into cell d15;
    - the source formula is entered as: ''[04-0101-INV.xls]TAB'!$J$11 (and as mentioned earlier, it does not begin with a quotation but rather two separate apostrophies so that one is kept with the formula)!!!

    Thanks once again for taking the time to help!

    Regards,

    Sheldon

  10. #10
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10

    Thanks you Mr. Boston!

    Quote Originally Posted by FrankBoston
    The following user defined function may help you

    ==========

    '---------
    ' Usage in cell: =gData("Book2.xls", "sheet2", C7)
    ' ........................... string ...... string . . . cell-reference
    ' Returns the value in Book2.xls, sheet2, cell C7
    ' ie the value of cell with hard address [Book2.xls]sheet2!C7
    '
    '
    Function gData(wb As String, ws As String, datacel As Range) As Variant
    gData = Workbooks(wb).Sheets(ws).Range(datacel.Address)
    End Function

    ==========


    How to install this user defined function
    - Open your workbook
    - Record a small macro
    - Find the macro and edit it
    - Replace all of the text of that macro with the above text that is
    between but not including the ========== lines
    - You can then use the function in a workbook cell

    Thanks for offering your solution to my desparate learning curve..... I was able to follow a solution through w/ and earlier thread but will certainly keep your macro for future application and reference.... I honestly have not delved into excel macros ever before, but as you know... there will always be a first time. Thanks for taking the time.

    Regards,

    Sheldon

  11. #11
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302
    I don't understand your exact problem, but here is another guess at a solution. Look at the formulas on the uploaded workbook, and tell me if it is on the right track.

    It would help if you uploaded an explanatory part of your workbook.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-26-2007
    Location
    Toronto, ON
    Posts
    10

    Thanks Frank

    Hi Frank;

    Thanks for offering a solution. I actually managed to solve this problem just before discovering a corruption somewhere in my windows registry.... I'm now in the process of upgrading my 5 year old computer. However in the meantime I have limited access to my installed programs as I can't get into my original profile due to the aforementioned corruption. I'm simply holding out til, I can rebuild and reinstall everything.

    Anyhow, thanks again for your help... my files are each over 2mb and will need some reworking in order to upload...

    Regards,

    Sheldon

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1