indexing formula not updating its reference automatically

[sebastian358]

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.  

[#2]

@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.

[#3]

@nimesht 

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 

[#4]

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

[#5]

@nimesht  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.