Context

I have a weird oddity with my spreadsheet. It's a report that pulls data from other spreadsheets, one workbook for each quarter. This pull is done using an index/match formula that looks at the given quarter's table. All workbooks are in the same directory on the network and when I initially set the index/match a dialog box popped up asking me to select the desire file.

The formula is

=INDEX('Directory-to-File[3rd Quarter Workbook.xlsx]Sheet1'!$H:$H,MATCH(J:J,'Directory-to-File[3rd Quarter Workbook.xlsx]Sheet1'!$B:$B,0))

The problem

One of the quarters' formula doesn't update when the quarter's workbook is updated. Furthermore, whenever you try to edit the cell (or drag/drop the formula down the column) the dialog box pops up asking me to select the workbook again. The other quarters do not have this problem.

Any ideas what leads to this difference?

5

1 Answer

When the file open dialog pops up, it's Excel asking the user to clarify the desired spreadsheet. However, if the dialog keeps popping up, then Excel cannot find the file as it is listed in the formula. Selecting the file via the dialog is a short term fix to get the index/match formula to work, but does not actually change the formula itself. IE, if there is a typo, the dialog box allows the user to select the file without the typo, but does not fix the formula so the issue will continue to rise.

Double check that the directory is correct and that the filename is spelled correctly. In this case, a letter e was omitted in the filename. Fixing the typo results in the dialog not popping up after editing the formula.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy