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?

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

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

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.

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