+ Reply to Thread
Results 1 to 4 of 4

Recursive links; workbooks that refer to workbooks that refer to workbooks..

  1. #1
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    366

    Cool Recursive links; workbooks that refer to workbooks that refer to workbooks..

    Hi guys,


    I think an example is best here because it will get very confusing other wise.
    • (Bottom level) Worksheet A - Original data
    • (The next level up) Worksheet B - Has named range to original data and prints it using CSE (Ctrl+Shift+Enter) within the workbook.
    • (the next level up from Worksheet B) Worksheet C - Has a reference Cell to worksheet B that is generated from Worksheet A's original data.
    • (I know this seems like the smart idea would be to have worksheet C reference Worksheet A, but that's not the point of this.)



    If worksheet B is opened without Worksheet A being opened, the CSE data comes up as #REF!, but any direct references will be updated.

    If worksheet C is opened, it will populate with information from Worksheet B. but if Worksheet B has not been opened with worksheet A, then the data in worksheet B will be wrong and so will Worksheet C.



    What this all boils down to is:
    I need a VBA that will find any all sources of data within the workbooks (Data>Connections >Edit Links), open those work books up, then find any sources of data within THOSE workbooks and also open them up, and continue this until everything is open. Then updates workbooks from the bottom up until the actual workbook I wanted to open up has the correct data.

    A better solution might be to build a list of directories and open files up with the level and the level above it to update the data, and then closes the bottom level.


    Anyways,
    if anyone has any ideas or a starting point' that would be great. Seems like a lot of work, but hopefully if it is done right should be usual in any worksheet.

    Thanks
    Jimmy
    Thanks,

    JimmyWilliams

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Recursive links; workbooks that refer to workbooks that refer to workbooks..

    A few points to note...
    1. if you change data in A, and dont open B, it does not know that A changed, and therefore, C will still be working on the "old" data from B
    2. if you change data in A, then save and close it, and then open B, it will update based on the new data in A, but you then MUST save B or it will still contain the old data. If you save and close B, then C will also update properly
    think if it this way...if B doesnt see what you did in A (you didnt open B), it wont know what you did
    3. B shows #REF errors if A isnt open. There are a few functions (INDIRECT, for 1) that need the referenced WB to be open, they dont work on closed WB's
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    366

    Re: Recursive links; workbooks that refer to workbooks that refer to workbooks..

    Quote Originally Posted by FDibbins View Post
    A few points to note...
    1. if you change data in A, and dont open B, it does not know that A changed, and therefore, C will still be working on the "old" data from B
    2. if you change data in A, then save and close it, and then open B, it will update based on the new data in A, but you then MUST save B or it will still contain the old data. If you save and close B, then C will also update properly
    think if it this way...if B doesnt see what you did in A (you didnt open B), it wont know what you did
    3. B shows #REF errors if A isnt open. There are a few functions (INDIRECT, for 1) that need the referenced WB to be open, they dont work on closed WB's
    Hi everything you have said is true.

    I'm hoping for a VBA macro I can run in worksheet C, which will then Open up worksheet B (repeat the process, and open up Worksheet A), Save Worksheet A, and Close it, Save WorkSheet B and Close it, and then Allows Worksheet C to have all the current information.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Recursive links; workbooks that refer to workbooks that refer to workbooks..

    You could probably do that by using Data tab/Edit Links/Open Source (then repeat for the other file.

    You could also record a macro that will do all of that for you, once you have all the files you need

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using INDIRECT to refer to different workbooks
    By Govind in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 06:05 PM
  2. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. Using INDIRECT to refer to different workbooks
    By Govind in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Using INDIRECT to refer to different workbooks
    By Govind in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  5. Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1