Excel not atomically updating

Resolved · Urgent Priority · Version 2016

Thu 31 Jan 2019, 14:57 Delegate Sehar said...

Sehar has attended:
Excel VBA Introduction course
Excel Advanced - Formulas & Functions course

Excel not atomically updating

I have created a spreadsheet which has many different formulas in it. I have lookup formulas at the top of my spreadsheet, see below. For some reason some of my lookup formulas are not calculating correctly, I also have some formulas which we just =a cell which were also not updating correctly. I have tried checking that auto formula updating is on which it is, I have checked the cell reference to make sure it's not formatted as text, I have tried F9, Cntrl Shift F9, Cntl Alt Shift F9 which does seem to refresh only one of the formulas but that's it. The only thing which seems to work is clicking in the cell and auto refreshing it but I can't do this as there are too many formulas to do this too and it means that my sheet is inaccurate as it's not calculating the formulas correctly. Any ideas?

Thanks

=IFERROR(LOOKUP(2,1/(BD10:BD100<>""),BD10:BD100),"")

Thu 31 Jan 2019, 19:51 Trainer Jens said...

RE: Excel not atomically updating

Hi Sehar,

Thank you for the forum question.

I have no good explanation to why your functions are not updating. If the workbook is set to automatic calculation the functions should update, but the Lookup function is an old function and Microsoft advice not to use it. It course a lot of problems in the workbook.

If you send the file to info@stl-training.co.uk, I can have a look at your file, and may be find a solution.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 1 Feb 2019, 09:18 Delegate Sehar said...

RE: Excel not atomically updating

Hello Jens,

I can’t send the file due to the information which is on it. Do you know of an alternative formula I can use to find the last value in a column of data? Perhaps if the lookup value is the issue then changing this might work?

Thanks

Fri 1 Feb 2019, 09:26 Trainer Jens said...

RE: Excel not atomically updating

Hi Sehar,

The best functions to lookup data are the combination Index Match.

It can be an issue with the lookup values. It must be an exact match to get it right. Please control you have exact matches.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Display developer tab - Excel 2010

a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes

View all Excel hints and tips