Forum Discussion
indexing formula not updating its reference automatically
hi, I have an excel sheet that uses an indexing formula to find info on the second sheet of the workbook. this second sheet is updated every so often by using another macro from another workbook....
sebastian358 Did you check if the Calculations Options is set to Automatic for the sheet where the formulas are not getting updated?
Manual calculation is preferred when the sheet is heavy with formulas and references from other workbooks/sheets etc. as it can take time to evaluate the formulas each time the file is saved, opened etc.
You can use F9 to re-evaluate the workbook of Shift+F9 for the sheet (the buttons next to the dropdown selected).
Use Like if this post helped to solve your issue and Mark as Best Response if the request can be closed.
hi Nimesht
Yes they are set to automatic also f9 only refreshes the sheet it doesn’t refresh the references. The formula is an indexing formula that uses the active cell and match’s that cells data with data on the second sheet, it then shows data from that row to get the formula to work you have to select the cell and then press f9 to refresh the data. The problem I’m encountering is when I update the data on the second sheet with more entries/ updates to the existing data I have to then re reference the sheet each time, however thank you for your response I appreciate you taking the time.
kind regards
- Hi Sebastian,
Are you working with multiple workbooks/files or different worksheets in the same file, as you mentioned "closing file", "file finder"...
The references don't update even if the other file is open in the background?
"Update Values" from Edit Links window also does not update the references?
I hope the Edit Links is set to Automatic (Default) value. Just checking, in case you missed it.
https://excelribbon.tips.net/T007556_Updating_Links.htmlnimesht so there is one sheet that I manually create and then I have a macro that copies that sheet into several other workbooks, the work books consist of two sheets the first sheet is the sheet that has the info boxes in it, the second sheet is the data sheet that the macro updates, the indexing formula is in the first sheet of the workbook (one in each box that displays data) when the macro is run from the original workbook to update the other workbooks it copies the data fine. (This process copies cells opens another workbook and then pastes and closes the workbook) however when I then open one of the now updates workbooks I have to re reference the indexing formula as if I don’t the data won’t show up on the first sheet, the data in the first sheet references the second sheet all inside the same workbook.
