formulas not updating after *single* refresh all

**jadamek02** · 07-01-2015, 02:41 PM

Hello Everyone,

Excel 2010

I am having an issue where some simple formulas do not update after a single data tab refresh all(ctrl + alt +F5). First I would like to apologize that I will not be able to attach the workbook because the external data is being retrieved from an ODBC source.

1. This tool starts with an outside query that returns results into a table.
2. Then there is a static list of items that are needed. There is a vlookup on this static list that will return an item from the outside query in step 1, if present.
3. Then there is a pivot table with the items from step 2.
4. Finally, there are some simple formulas count, sum, subtraction, etc that are calculated on the items in the pivot table.

I refresh the query and everything works up until the simple formulas in step 4. I must refresh twice to get these calculations to update. I have tried to write a macro to update twice, but I am still getting the same problem. It will not update until I run the macro twice. I have tried putting in a time delay in the macro in between refreshes and still no luck.

Please Login or Register  to view this content.

The main line in there is:

Please Login or Register  to view this content.

Any thoughts or suggestions?

**GeneralDisarray** · 07-01-2015, 03:10 PM

There's a way to "pause" the code until the refresh is completed (from the ODBC source).

I think you can handle it in the refresh line in your VBA code, by setting a parameter 'BackgroundQuery:=False' -- but you can also set that in the query table itself. Click on the Query table so the "table tools" shows up in the ribbon, then click on Design> Refresh > Connection Properties - then uncheck "Enable Background Refresh".

---
Basically, the code is cruising along without the new data - this will make sure the next line is not executed too soon.

Not 100% sure if the call you have would take that parameter -- here's how I used it at one point:

Please Login or Register  to view this content.

I guess I was refreshing each table that explicitly named to be a database view. I think the reason I did it this way instead of with a 'refreshAll' was I couldn't be sure each table was configured to not allow background refresh.

**jadamek02** · 07-22-2015, 09:20 AM

Thank you very much. This has been solved.

formulas not updating after single refresh all

LinkBack

Thread Tools

Rate This Thread

Display