Problem with Linking to External Workbook

Topic

New Reply

I am a new lounger. I am working with a couple of workbooks that contain VBA macros and were created by another unavailable individual. The macros create and name worksheets in the workbook based on a download of information from a mainframe application. IF a particular “classification” does not exist in any given month a worksheet for that classification does not get set up. The linking worksheet contains a formula looking to a cell on the “absent” worksheet on the external workbook. This produces an error message #REF!. Tried using ISERROR to evaluate whether the formula would produce the error #REF! and return a 0 if so otherwise calculate the formula. Except …EXCEL doesn’t seem to like the idea that the worksheet does NOT exist in the external workbook when ISERROR is used. Any ideas for a work around??? Any and all help will be appreciated.
Thanks, Chance

Reply | Quote

Replies

A suggestion:
The following code will list all of the worksheets in the file Investments and put their names in the range SheetNames (which needs to be in the active sheet):

Sub ListSheets()
Dim s As Worksheet, i As Integer
Range(“SheetNames”).ClearContents
i = 1
For Each s In Workbooks(“Investments.xls”).Sheets
Range(“SheetNames”).Cells(i, 1) = s.Name
i = i + 1
Next
End Sub

After running this macro, you could then set your formulae along the lines of:

if(not(isna(match(“Classification”,SheetNames,0))),A+B,”Classification not found”)

This way you avoid the problem of references being there or not.

If anything above is not clear, just say.

Jon

Reply | Quote

Thanks Jon … I think I can follow your code ….I will try it. I am just getting my arms around VBA beyond the very basic …. I don’t have the worksheets at home so I won’t be able to “Test” it completely until Tuesday sometime. I really appreciate your help. I’ll post later this weekend after I play with it a little. Thanks again.

Reply | Quote

You’re welcome. As I said, if you need further explanation,just let us know.

Jon

Reply | Quote

Jon … I’ve played with the code and formula here and tested it a couple of times; I think it’ll work for what we need. There is one part of the formula I’m not quite sure I understand but I want to try to think it through myself rather than ask … I learn better that way. IF I can’t I will ask. Thanks once again and maybe someday I can return the favor.

Reply | Quote

Jon … I played a little more and found that I wind up with a “#REF! error now if the sheet is not there because the calculation has to come from the sometimes there and sometimes not there sheet. Here is a sample of the formula

=IF(NOT(ISNA(MATCH(“Classification”,SheetNames,0))),SUM(‘[Investments.xls]Classification’!$B$3:$B$10),0)

Any other ideas? I’m thinking too.

Reply | Quote

Ok. What you need to do is get Excel to only work out the address that you are summing when the sheet exists. You can do this using the Address and Indirect functions. The following example assumes that the classification you are looking for is in cell F7:

=IF(NOT(ISNA(MATCH(F7,SheetNames,0))),SUM(OFFSET(INDIRECT(ADDRESS(1,6,1,1,”[investments.xls]”&F7)),0,0,10)),0)

See how you go with that.

Jon

Reply | Quote

Jon,
I got this to work … but it presumes that the worksheets in the source workbook set up in the same order each month because of the specific reference in the match. I’m not sure this is the case. I’ll try to test it on the actual workbooks on Tuesday and let you know … I appreciate your help and expertise. Thanks!

Reply | Quote

Jon …
I have one more question … the Absolute Reference in the Indirect/Address may be a problem. Since the VBA code goes and gets the existing worksheets in the source workbook that will vary each month. For Example

Month 1 gets
K1 Ansonville
K2 Asheboro
K3 Asheville
K4 Brevard
K5 Burnsville

Month 2 may only get
K1 Ansonville
K2 Asheville
K3 Burnsville

Such that the cell location on the match will vary from month to month. Could I somehow combine a VLOOKUP within the Address formula to get the cell reference?

Let me know what you think … or any other ideas/suggestions you might have. In the meantime …I’m going to play some and see if I can figure out how to use the VLOOKUP.

Thanks once again.
Chance

Reply | Quote

Jon

I think I might have it solved …. I substituted the word “Classification” in Quotes ….in both places of the formula the had the F7 cell reference. Now … after running the macro …. not matter where the sheet “Classification” is … the formula finds it … if it doesn’t exist …. it returns a 0 which is what we want.

Thanks so much for your help with this.
Chris

Reply | Quote