VLOOKUP Not Updating The Values Automatically

Copper Contributor

Hi Team,

 

I've used the Vlook-up formula in an Excel sheet & the values have been reflected accurately. However, the data is not getting updated automatically as it always asks for enable content as data connections have been disabled. I've enabled all the connections from the trust center settings but still, the changes aren't getting updated automatically as it asks to Enable content always & only then does the data get updated. Could someone help me with this?

Regards,
Rohit

4 Replies

@rohit6694 

Here are a few troubleshooting steps you can try:

 

    Check your Excel Options settings. Under the Formulas section, make sure that "Enable iterative calculation" is checked. This can help Excel automatically recalculate your VLOOKUP formula when there are changes in the data.

 

    Check your data connections. Go to the Data tab, click on "Connections," and make sure that your data connections are set to refresh automatically. You can also manually refresh the connections from this menu.

 

    Check for any errors in your VLOOKUP formula. If there are errors, it can prevent Excel from updating the values automatically. Make sure that your formula references the correct cells and ranges, and that the data you are looking up is in the correct format.

 

    Check your security settings. Sometimes Excel may not update external data connections if your security settings are too high. Go to the Trust Center settings and make sure that "Enable all data connections" is selected.

 

If none of these steps work, you may need to provide more information about your Excel sheet, Excel version, operating system, storage medium and the data connections you are using in order to troubleshoot the issue further.

@NikolinoDE

I've already tried all of them & they are enabled as it is. Let me share my details for you to troubleshoot further.
Excel Version - Microsoft 365 16.0.16130.20394
OS Version - 10.0.19044 Build 19044 Microsoft Windows 10 Enterprise
Storage Medium - Virtual (14.1GB)
Data connections - Not sure of this, please can you elaborate?
I will wait for a response from your end.
Regards,
Rohit

@rohit6694 

If data connections have been disabled and Excel is always asking you to enable content, it may prevent the automatic update of the data.

You can try enabling all data connections from the Trust Center settings and make sure that your data connections are set to refresh automatically.

To do this, go to the Data tab, click on "Connections," and make sure that your data connections are set to refresh automatically.

You can also manually refresh the connections from this menu.

 

If you have already tried these and the other steps before and the issue still persists, I'm at my wits end there too.

 

I would recommend reaching out to Microsoft Support for further assistance.

@NikolinoDE 

Ok

Yes there is a excel bug and updating vlookup does not work automatically.

Office 365 has some "great" goals and returns alot of  money to microsoft but does not fulfill simple things like some refreshes, calculations and delivers  ide bugs and others(to be short). I work in engineering field and excel has helped me spend some extra time for solving small problems like refreshing multiple vlookups in multiple sheets and without external links or such.

Everything was on autocalculation. Nothing on forums worked (so help and assistance=0, sorry microsoft!)  

Simple solution:

Use old find and replace

search for  "VLOOKUP" in all workbook and replace it with "VLOOKUP" after setting the search type to formula.

That's all.

Good one Microsoft!

Maybe it will change in OFFICE 366 (Can't wait!)