Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with Linking to External Workbook

    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

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

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

    Jon

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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(I NDIRECT(ADDRESS(1,6,1,1,"[investments.xls]"&F7)),0,0,10)),0)

    See how you go with that.

    Jon

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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!

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Linking to External Workbook

    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

Posting Permissions

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