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

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

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

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)

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

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

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

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?