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),"")
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
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
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
