Is there any way around this? (#REF in some external references)

TomCon

Active Member
Joined
Mar 31, 2011
Messages
307
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,259
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top