If you reference a cell in an external workbook, if that workbook is closed, the cell retains the last value that it had before the workbook was closed.
So this formula:
=[D5_TRK.xlsm]data!$A$1
retains the value it had when both workbooks were open. When you later may open D5_TRK.xlsm, if the value has changed in the meantime, it is updated when that book is opened.
Also an INDEX formula retains its value and does not revert to #REF when the external book is closed, if the row and column arguments are constants.
=INDEX(D5_TRK.xlsm!rng_data[#Data],230,1) 'This formula does not update to #REF when the external book is closed. (rng_data is a Table in the external book). But this formula uses constants for row, column so it will not serve me, but it was an experiment to see what happens.
But using the INDEX function with a non-constant in the row argument, I get the behavior that immediately when you close the externally referenced book (in this case D5_TRK.xlsm), the referencing formula becomes #REF. I do not want this to happen and want the INDEX function to retain its last value when the external book is closed, not update to #REF.
I have experimented with many different versions of the INDEX formula and cannot find one that uses variables for the row and column argument to INDEX, that do not revert to #REF when the external book is closed. Any suggestions to accomplish this?
=INDEX(D5_TRK.xlsm!rng_data[#Data],230,COLUMN(D5_TRK.xlsm!rng_data[[#Headers],[Dte]])) 'This formula also does not update to #REF when the book is closed.
=INDEX(D5_TRK.xlsm!rng_data[#Data],D5TRK_Todayrow-121,1) ' This does update to #REF when the book is closed, when the row argument is not a constant. D5TRK_todayrow is a name defined in the current workbook (not the external one) but it is a reference to a cell in the external book. Its definition is: =[D5_TRK.xlsm]defines!$F$4
=INDEX([D5_TRK.xlsm!rng_data[#Data],D5_TRK.xlsm!TodayRow-121,COLUMN(D5_TRK.xlsm!rng_data[[#Headers],[Dte]])) 'This updates to #REF when the external book is closed. It references the name from the external book itself, not a name in the current book that references a cell in the external book.
This one i really thought might be a workaround. In a cell in the current book i merely referenced the "todayrow" cell in the external book with a formula. It is in cell Q4, the formula is: =D5_TRK.xlsm!TodayRow. Then, i referenced that cell in the INDEX formula. The cell Q4 does retain its value and not revert to #REF when the external book is closed. But the INDEX formula still fails and reverts to #REF. I even tried changing the COLUMN function to a constant, and this did not help.
=INDEX(D5_TRK.xlsm!rng_data[#Data],$Q$4-121,1) 'Reverts to #REF when external book is closed.
Does anybody know how i can get the row argument of INDEX to contain a reference to some version of the name "todayrow" (which changes daily in the external workbook)? Or some workaround for this issue. I want the current book to retain the last value that was present the last time that the external book was open, not immediately revert to #REF. In other words to behave the same as the first or second example formulas given, that do not revert to #REF when the external book is closed.
Thank you for looking at this somewhat complex/intricate example.
'
ffffff
So this formula:
=[D5_TRK.xlsm]data!$A$1
retains the value it had when both workbooks were open. When you later may open D5_TRK.xlsm, if the value has changed in the meantime, it is updated when that book is opened.
Also an INDEX formula retains its value and does not revert to #REF when the external book is closed, if the row and column arguments are constants.
=INDEX(D5_TRK.xlsm!rng_data[#Data],230,1) 'This formula does not update to #REF when the external book is closed. (rng_data is a Table in the external book). But this formula uses constants for row, column so it will not serve me, but it was an experiment to see what happens.
But using the INDEX function with a non-constant in the row argument, I get the behavior that immediately when you close the externally referenced book (in this case D5_TRK.xlsm), the referencing formula becomes #REF. I do not want this to happen and want the INDEX function to retain its last value when the external book is closed, not update to #REF.
I have experimented with many different versions of the INDEX formula and cannot find one that uses variables for the row and column argument to INDEX, that do not revert to #REF when the external book is closed. Any suggestions to accomplish this?
=INDEX(D5_TRK.xlsm!rng_data[#Data],230,COLUMN(D5_TRK.xlsm!rng_data[[#Headers],[Dte]])) 'This formula also does not update to #REF when the book is closed.
=INDEX(D5_TRK.xlsm!rng_data[#Data],D5TRK_Todayrow-121,1) ' This does update to #REF when the book is closed, when the row argument is not a constant. D5TRK_todayrow is a name defined in the current workbook (not the external one) but it is a reference to a cell in the external book. Its definition is: =[D5_TRK.xlsm]defines!$F$4
=INDEX([D5_TRK.xlsm!rng_data[#Data],D5_TRK.xlsm!TodayRow-121,COLUMN(D5_TRK.xlsm!rng_data[[#Headers],[Dte]])) 'This updates to #REF when the external book is closed. It references the name from the external book itself, not a name in the current book that references a cell in the external book.
This one i really thought might be a workaround. In a cell in the current book i merely referenced the "todayrow" cell in the external book with a formula. It is in cell Q4, the formula is: =D5_TRK.xlsm!TodayRow. Then, i referenced that cell in the INDEX formula. The cell Q4 does retain its value and not revert to #REF when the external book is closed. But the INDEX formula still fails and reverts to #REF. I even tried changing the COLUMN function to a constant, and this did not help.
=INDEX(D5_TRK.xlsm!rng_data[#Data],$Q$4-121,1) 'Reverts to #REF when external book is closed.
Does anybody know how i can get the row argument of INDEX to contain a reference to some version of the name "todayrow" (which changes daily in the external workbook)? Or some workaround for this issue. I want the current book to retain the last value that was present the last time that the external book was open, not immediately revert to #REF. In other words to behave the same as the first or second example formulas given, that do not revert to #REF when the external book is closed.
Thank you for looking at this somewhat complex/intricate example.
'
ffffff
