+ Reply to Thread
Results 1 to 3 of 3

Problem with Relative Referencing. Do not work when only the source book is open.

  1. #1
    Registered User
    Join Date
    08-15-2016
    Location
    Roy Utah
    MS-Off Ver
    2016
    Posts
    1

    Problem with Relative Referencing. Do not work when only the source book is open.

    Plain and simple, causing a HUGE problem for me.

    I have workbooks 1, 2, 3, and 4.

    We'll call 1 the master, and 2-4 the pawns.

    2, 3, and 4 contain formulas like

    =[1.xlsx]Sheet1!$A$1 (In cell A1)
    =[1.xlsx]Sheet1!$A$2 (In cell A2)
    etc....

    Basically 2, 3, and 4 will all look like book 1. A user just needs to modify book 1 and the others will match.

    ****
    ****

    Here we're setting up for the problem:

    All workbooks are on a shared drive, and there could any number of pawns added (that will match any data entered into the master)

    Each pawn workbook is customized by it's owner to see only the names for the employees in their area.

    When we get a new employee, I will need to insert a row into the master, lets say into row 2. EXAMPLE BELOW:

    BEFORE:

    -the master Book looks like this:
    billy (In cell A1)
    joe (In cell A2)
    henry (In cell A3)
    kent (In cell A4)
    bob (In cell A5)

    all of the pawns contain these formulas:
    =[1.xlsx]Sheet1!$A$1 (In cell A1)
    =[1.xlsx]Sheet1!$A$2 (In cell A2)
    =[1.xlsx]Sheet1!$A$3 (In cell A3)
    =[1.xlsx]Sheet1!$A$4 (In cell A4)
    =[1.xlsx]Sheet1!$A$5 (In cell A5)

    and look like this

    billy (In cell A1)
    joe (In cell A2)
    henry (In cell A3)
    kent (In cell A4)
    bob (In cell A5)

    AFTER INSERTING A NEW ROW FOR A NEW PERSON:

    -the master Book looks like this:
    billy (In cell A1)
    Noob (In cell A2)
    joe (In cell A3)
    henry (In cell A4)
    kent (In cell A5)
    bob (In cell A6)

    all of the pawns contain these formulas:

    =[1.xlsx]Sheet1!$A$1 (In cell A1)
    =[1.xlsx]Sheet1!$A$3 (In cell A2) <----Here is where the relative formulas change. This is exactly what I want to happen.
    =[1.xlsx]Sheet1!$A$4 (In cell A3)
    =[1.xlsx]Sheet1!$A$5 (In cell A4)
    =[1.xlsx]Sheet1!$A$6 (In cell A5)

    Each pawn's cells still look like this:

    billy (In cell A1)
    joe (In cell A2)
    henry (In cell A3)
    kent (In cell A4)
    bob (In cell A5)

    This is how it works, and it works great.

    HERE IS THE PROBLEM:

    When I insert a new employee into the master, the relative cells will not change unless those pawns files are open. EXAMPLE BELOW:

    After inserting a new row for a new person on the master workbook ***And no pawn workbooks are open:

    -the master Book looks like this:
    billy (In cell A1)
    Noob (In cell A2)
    joe (In cell A3)
    henry (In cell A4)
    kent (In cell A5)
    bob (In cell A6)

    Then you open any of the pawns and they contain these formulas:

    =[1.xlsx]Sheet1!$A$1 (In cell A1)
    =[1.xlsx]Sheet1!$A$2 (In cell A2) <----No changes were made on any of the relative formulas.
    =[1.xlsx]Sheet1!$A$3 (In cell A3) <----
    =[1.xlsx]Sheet1!$A$4 (In cell A4) <----
    =[1.xlsx]Sheet1!$A$5 (In cell A5) <----

    Pawn's data in all the cells below row 2 on the master are shifted down SHOWING ME THE WRONG NAMES:

    billy (In cell A1)
    Noob (In cell A2) <----I want these name to remain unchanged like you saw in the first example. But the relative formula never changed as a result of the file not being open during the modification in the master.
    joe (In cell A3)
    henry (In cell A4)
    kent (In cell A5)

    PLEASE let me know if this was clear enough.

    Any help is much appreciated.

    Thanks,
    Dallin

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Problem with Relative Referencing. Do not work when only the source book is open.

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    To add a file to a post

  3. #3
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Problem with Relative Referencing. Do not work when only the source book is open.

    Relative reference will not change if your Pawns are closed.

+ 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. Replies: 6
    Last Post: 07-03-2013, 10:44 AM
  2. Want to Hide a UserForm source book while keeping main XL workbook open...
    By MBlaster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2012, 05:52 PM
  3. Replies: 2
    Last Post: 06-20-2012, 05:10 AM
  4. Referencing information in a excel work book.
    By starlinepc in forum Excel General
    Replies: 1
    Last Post: 06-18-2008, 11:39 AM
  5. Referencing cells from another work book
    By NathanLedet in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-25-2006, 03:17 PM
  6. open work book
    By nowfal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2005, 06:42 AM
  7. [SOLVED] incorporating live data from external source to work book
    By Jess in forum Excel General
    Replies: 0
    Last Post: 02-10-2005, 02:06 PM
  8. [SOLVED] Relative referencing in macro won't work
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2005, 10:06 AM

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