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
Bookmarks