Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

NETWORKDAYS VBA issue

Options
  • 13-05-2009 3:06pm
    #1
    hallelujah
    Closed Accounts Posts: 584 ✭✭✭


    Hi all,

    I am creating an excel spreadsheet via a macro. One of the columns uses the NETWORKDAYS formula.

    I have a workbook called WKBA which has 2 sheets, SheetA + SheetB.

    SheetA has the networkingdays formula which references a second sheet, SheetB, e.g.

    =NETWORKDAYS(A1,B1,SheetB!M1:Z1)


    I then copy SheetA into a new workbook (WKBB). The new workbook also has a reference sheet called 'SheetB' (same format, data and name).

    When I now view the networkingdays formula in WKBB, it is referencing the wrong reference sheet, e.g.

    =NETWORKDAYS(A1,B1,'[WKBA]SheetB'!M112:Z112)

    instead of

    =NETWORKDAYS(A1,B1,'[WKBB]SheetB'!M112:Z112), i.e. its own SheetB not the original one.

    Do any of ye know how to get it to reference its own SheetB? I have tried the following but it doesn't work:

    =NETWORKDAYS(A1,B1,'[this]SheetB'!M112:Z112)


    I'd appreciate any help,
    Jeff


Comments

  • Options
    #2
    IamtheWalrus
    Closed Accounts Posts: 6,075 ✭✭✭IamtheWalrus


    mmmm, you need a way of getting the original sheet to forget the original reference and pick up the new SheetB..


  • Options
    #3
    MOH
    Registered Users Posts: 6,465 ✭✭✭MOH


    From this, there doesn't seem to be any way to do it other than changing the references after you copy the sheet.


    The only way I can come up with to get it to work is messier, using the indirect function. (I don't have the NETWORKDAYS function, maybe it's new in 2007, I'm on 2003, but I've tested this using SUM)

    - In one of your cells (say ZZ99), enter your reference to the other sheet as text, so SheetB!M1:Z1 (not =SheetB!M1:Z1)
    - Change your formula to =NETWORKDAYS(A1,B1,INDIRECT(ZZ99))

    INDIRECT gets replaced with the contents of the cell ZZ99, so that effectively reads:
    =NETWORKDAYS(A1,B1,SheetB!M1:Z1)

    *but* it works when you copy the sheet to another workbook - it references SheetB in the new book

    Might work for you if it's only this one formula, or if you copy the sheet between workbooks a lot, but if it's a one off you might be better doing a search/replace or using a macro rather than changing multiple formulas to use the INDIRECT function.


  • Options
    #4
    hallelujah
    Closed Accounts Posts: 584 ✭✭✭hallelujah


    MOH wrote: »
    From this, there doesn't seem to be any way to do it other than changing the references after you copy the sheet.


    The only way I can come up with to get it to work is messier, using the indirect function. (I don't have the NETWORKDAYS function, maybe it's new in 2007, I'm on 2003, but I've tested this using SUM)

    - In one of your cells (say ZZ99), enter your reference to the other sheet as text, so SheetB!M1:Z1 (not =SheetB!M1:Z1)
    - Change your formula to =NETWORKDAYS(A1,B1,INDIRECT(ZZ99))

    INDIRECT gets replaced with the contents of the cell ZZ99, so that effectively reads:
    =NETWORKDAYS(A1,B1,SheetB!M1:Z1)

    *but* it works when you copy the sheet to another workbook - it references SheetB in the new book

    Might work for you if it's only this one formula, or if you copy the sheet between workbooks a lot, but if it's a one off you might be better doing a search/replace or using a macro rather than changing multiple formulas to use the INDIRECT function.

    Thanks man. I used another method where I saved off a copy and deleted the unrequired sheets.


Advertisement