Hello I need help with issues I'm external links to a table when table rows are deleted and the reference workbook (book1) is not open.
below Book2 has formula that references table in Book1. When a table row/rows are delete in Book1 saved and closed then if the Book2 is opened without Book1 opened the data in Book2 is incorrect. I have noticed that the results shift up the amount of rows being deleted.
ex:
if table row of "KIARA BRACEY" & "JACOB KACHINKO" are deleted the results from Book2 Values move up for everyone below the the agents. DONALD GARNER in BOOK2 Leave will become JACOB MORRIS
Updating Links every time runs very slow due to the amount of data External Links (over 1000 Cells) in BOOK2 have
Here are some solutions I think will solve this that I can not figure out
Is there a way to update Links quickly, or change my formula to solve this, or VBA code in Book2 to open Book1 (in read only)
Let me know if I need to clarify anything, any help will be greatly appreciated.
below Book2 has formula that references table in Book1. When a table row/rows are delete in Book1 saved and closed then if the Book2 is opened without Book1 opened the data in Book2 is incorrect. I have noticed that the results shift up the amount of rows being deleted.
ex:
if table row of "KIARA BRACEY" & "JACOB KACHINKO" are deleted the results from Book2 Values move up for everyone below the the agents. DONALD GARNER in BOOK2 Leave will become JACOB MORRIS
Updating Links every time runs very slow due to the amount of data External Links (over 1000 Cells) in BOOK2 have
Here are some solutions I think will solve this that I can not figure out
Is there a way to update Links quickly, or change my formula to solve this, or VBA code in Book2 to open Book1 (in read only)
Let me know if I need to clarify anything, any help will be greatly appreciated.
| Book1.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
| 1 | FIRST NAME | LAST NAME | FULL NAME | LEAVE | STARTS | ENDS | D1 | D2 | D3 | D4 | D5 | D6 | D7 | D8 | D9 | D10 | D11 | D12 | D13 | D14 | D15 | D16 | D17 | D18 | D19 | D20 | D21 | D22 | ||
| 2 | JEFFREY | CARLSON | JEFFREY CARLSON | 9/13 | 9/14 | 9/15 | ||||||||||||||||||||||||
| 3 | CORNELIUS | EPLEY | CORNELIUS EPLEY | |||||||||||||||||||||||||||
| 4 | DAISUKE | IRVIN | DAISUKE IRVIN | MILL | 1/1/2023 | 12/31/2023 | 5/24 | 5/25 | 5/26 | 8/4 | 8/7 | 8/8 | 8/9 | |||||||||||||||||
| 5 | LEO | WILLIS | LEO WILLIS | 3/16 | 3/30 | 3/29 | 5/29 | 6/17 | 6/16 | 6/15 | 6/14 | 7/25 | 7/26 | 7/27 | 7/28 | 7/31 | ||||||||||||||
| 6 | JOSHUA | SECKMAN | JOSHUA SECKMAN | MLA | 6/15/2022 | 12/6/2023 | ||||||||||||||||||||||||
| 7 | DEADERA | WALDEN | DEADERA WALDEN | 2/8 | 2/9 | 2/10 | 3/16 | 3/17 | 4/5 | 5/4 | 5/5 | 6/13 | 6/14 | 6/15 | 6/18 | 8/18 | 9/1 | 9/7 | 9/14 | 9/21 | 9/22 | |||||||||
| 8 | KIARA | BRACEY | KIARA BRACEY | 7/5 | 7/6 | 7/7 | 7/8 | 8/13 | 8/23 | |||||||||||||||||||||
| 9 | JACOB | KACHINKO | JACOB KACHINKO | 3/13 | 3/14 | 3/15 | 3/16 | 3/17 | 6/16 | 7/13 | 7/14 | 7/17 | 7/18 | 8/4 | 8/7 | 8/8 | 8/9 | 8/10 | 8/11 | 8/14 | 8/15 | 8/16 | ||||||||
| 10 | LAURENTH | McHUGH | LAURENTH McHUGH | 6/16 | 6/26 | 7/3 | 7/4 | 7/9 | 8/6 | 8/25 | 8/24 | 8/18 | ||||||||||||||||||
| 11 | DASHEAN | MOBLEY | DASHEAN MOBLEY | MLA | 11/15/2022 | 12/31/2023 | ||||||||||||||||||||||||
| 12 | CARY | HARTMAN | CARY HARTMAN | 8/12 | 9/5 | |||||||||||||||||||||||||
| 13 | JENNIFER | EVERETT | JENNIFER EVERETT | FMLA | 8/1/2023 | 9/20/2023 | 3/30 | 3/31 | 4/1 | 6/1 | 6/4 | 6/5 | 6/6 | 6/13 | 8/1 | 8/2 | 8/3 | 8/6 | 8/7 | 8/8 | 8/9 | 8/10 | 8/13 | 8/14 | ||||||
| 14 | JORDAN | BURGOYNE | JORDAN BURGOYNE | OJI | 8/1/2023 | 9/4/2023 | 1/16 | 1/23 | 2/27 | 2/13 | 2/14 | 3/1 | 4/24 | 4/25 | 4/13 | 4/14 | 5/3 | 6/20 | 7/4 | 8/7 | ||||||||||
| 15 | THOMAS | SMALLWOOD | THOMAS SMALLWOOD | 6/24 | 8/11 | 8/12 | 8/24 | |||||||||||||||||||||||
| 16 | CAROL | WADE | CAROL WADE | 1/23 | 1/27 | 1/30 | 2/9 | 2/8 | 2/21 | 5/29 | 5/30 | 6/12 | 6/19 | 6/8 | 6/15 | 7/4 | 7/5 | 7/20 | 7/21 | 7/24 | 6/28 | 7/11 | 7/13 | 7/19 | 7/18 | |||||
| 17 | LUCAS | LIVINGSTON | LUCAS LIVINGSTON | 3/10 | 3/11 | 4/8 | 4/22 | 5/27 | 5/13 | 6/11 | 6/8 | 6/7 | 6/6 | 6/5 | 6/4 | 7/16 | 7/23 | 8/13 | 8/20 | 8/27 | ||||||||||
| 18 | CHARLES | McHUGH | CHARLES McHUGH | 6/13 | 6/14 | 6/15 | 6/16 | 6/2 | 7/4 | 8/8 | 9/2 | 9/5 | 9/6 | 9/7 | 9/8 | 9/9 | 9/18 | 9/22 | ||||||||||||
| 19 | DODRICK | HIXSON | DODRICK HIXSON | 5/15 | 5/22 | 5/29 | 5/30 | 6/1 | 6/2 | 6/5 | 6/12 | 6/19 | 6/26 | 5/31 | 7/4 | 7/5 | 7/10 | 7/17 | 7/24 | 7/31 | 8/4 | 8/6 | 8/13 | 8/20 | 8/27 | |||||
| 20 | JOSEPH | PRICE | JOSEPH PRICE | 2/5 | 2/12 | 3/5 | 3/12 | 4/9 | 5/7 | 5/14 | 7/2 | 9/13 | ||||||||||||||||||
| 21 | ANTWAN | KIMES | ANTWAN KIMES | 7/15 | 7/22 | 7/29 | 7/18 | 7/21 | 8/5 | 8/12 | 8/19 | 9/10 | 9/17 | 9/24 | 9/11 | |||||||||||||||
| 22 | ADRIAN | HUFFAKER | ADRIAN HUFFAKER | 6/17 | 9/17 | 9/18 | 9/19 | 9/23 | 9/24 | |||||||||||||||||||||
| 23 | BRYCE | ISRAEL | BRYCE ISRAEL | 1/23 | 2/10 | 3/27 | 4/21 | 4/28 | 3/30 | 4/12 | 7/9 | 7/10 | 7/11 | 7/12 | 7/13 | 8/30 | 8/31 | |||||||||||||
| 24 | CHRISTINA | BYERS | CHRISTINA BYERS | 6/16 | 6/23 | 6/30 | 7/7 | 7/13 | 7/14 | 8/2 | 8/18 | 8/16 | 8/10 | 9/2 | 9/9 | 9/23 | ||||||||||||||
| 25 | THOMESHA | SARGENT | THOMESHA SARGENT | FNL | 8/15/2023 | 9/14/2023 | 2/23 | 2/24 | 2/27 | 1/26 | 3/6 | 3/31 | 5/4 | 5/5 | 7/7 | 7/8 | 7/28 | 7/31 | 9/1 | 9/4 | 9/22 | 9/25 | ||||||||
| 26 | JACOB | MORRIS | JACOB MORRIS | 4/2 | 4/21 | 4/22 | 4/23 | 5/11 | 5/12 | 5/13 | 5/14 | 6/27 | 6/28 | 6/29 | 6/30 | 7/3 | 7/4 | 8/13 | 8/23 | 8/24 | 8/25 | |||||||||
| 27 | JOANNA | BREY | JOANNA BREY | 3/12 | 3/19 | 3/26 | 3/15 | 3/16 | 4/9 | 5/7 | 5/14 | 6/12 | 6/16 | 6/19 | 5/27 | 5/28 | 7/3 | 7/4 | 7/5 | 7/21 | 7/28 | 7/31 | 9/4 | 8/25 | ||||||
| 28 | DONALD | GARNER | DONALD GARNER | OJI | 6/27/2023 | 12/31/2023 | 9/15 | |||||||||||||||||||||||
| 29 | CHRISTOPHER | MEISCH | CHRISTOPHER MEISCH | 6/14 | 6/17 | 6/18 | 6/19 | 6/20 | 6/21 | 8/12 | 9/10 | 9/24 | ||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C29 | C2 | =CONCATENATE([@[FIRST NAME]]," ",[@[LAST NAME]]) |
| Book2.xlsx | ||||
|---|---|---|---|---|
| J | K | |||
| 1 | 8-Aug | |||
| 2 | ||||
| 3 | VAC | LEAVE | ||
| 4 | DAISUKE IRVIN | DAISUKE IRVIN | ||
| 5 | JACOB KACHINKO | JOSHUA SECKMAN | ||
| 6 | JENNIFER EVERETT | DASHEAN MOBLEY | ||
| 7 | CHARLES McHUGH | JENNIFER EVERETT | ||
| 8 | JORDAN BURGOYNE | |||
| 9 | DONALD GARNER | |||
| 10 | ||||
| 11 | ||||
| 12 | ||||
| 13 | ||||
| 14 | ||||
| 15 | ||||
| 16 | ||||
| 17 | ||||
| 18 | ||||
| 19 | ||||
| 20 | ||||
| 21 | ||||
| 22 | ||||
| 23 | ||||
| 24 | ||||
| 25 | ||||
| 26 | ||||
| 27 | ||||
| 28 | ||||
Sheet2 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4:J28 | J4 | =IFERROR(INDEX(Book1.xlsx!Elist[FULL NAME],AGGREGATE(15,6,(ROW(Book1.xlsx!Elist[FULL NAME])-ROW([Book1.xlsx]Sheet1!$C$2)+1)/((Book1.xlsx!Elist[[D1]:[F10]]=$J$1)),ROWS($J$4:J4))),"") |
| K4:K28 | K4 | =IFERROR(INDEX(Book1.xlsx!Elist[FULL NAME],AGGREGATE(15,6,(ROW(Book1.xlsx!Elist[FULL NAME])-ROW([Book1.xlsx]Sheet1!$C$2)+1)/((Book1.xlsx!Elist[STARTS]<=$J$1)*(Book1.xlsx!Elist[ENDS]>=$J$1)),ROWS(K$4:K4))),"") |
