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. when I use the updating macro it copies info from 1 sheet into several others by opening them unprotecting them copy and past info then re protecting them and finally closing them. the copy and paste works fine, but when you then go into the sheet and try and look at the new data, it doesn't show up in the indexing formula even though it is physically there on the second sheet. I have to go into the formula and press enter, it then brings up the file finder and I have to reselect the sheet each time.
my question being is there a way to get the sheet to automatically re-reference the sheet after an update? or do I have to manually open and re reference every sheet each time there is an update.
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.html
