One particular excel cell formula not auto-calculating

[Derek1974]

Hi all,

First time on the community, so both excited and a bit nervous that I am putting this in the right way / place.

Anyway, I have done a search for this question before, but I am not able to find a suggestion to my specific problem.

I have built a rudimentary little weight loss tracker spreadsheet (lots of new year resolutions in the family that I am building them (and me!!!) a wee tracker) and for the most part, works well. 

However, there is one particular cell, a LOOKUP formula where it returns the value from the last cell with data in a column, that is not auto-updating when data is entered to it's reference cells.  I do have automatic calculations turned on, and the adjacent cells (again with a similar LOOKUP formula) DO auto-update.  

The formula itself works fine, as it will update if I enter the data bar and press return, it just won't do it automatically UNLESS I add some data to the reference cells, then clear it.

Any help or suggestions gratefully received.

Many thanks

Derek

[#2]

@Derek1974Not sure what you meant in the last sentence or two by adding to the cells and clearing it - but typically if it is the only cell it could be caused by being formatted as text (check the number formatting on the Home tab, or maybe a space or a ' at the beginning of the formula.

[#3]

@Derek1974 

If Alan's suggestion didn't resolve your situation, it would help if you could upload a copy of your actual spreadsheet so we could diagnose on the actual "patient"

In general, no matter how good your description might be, a sample (devoid of private information) of the actual spreadsheet goes a long way toward getting a meaningful answer.  (Besides, I'm curious how a LOOKUP formula is being used in this context: are you using the comparison of the most recent day's weight with, say, weight a week prior, to come up with words of encouragement or challenge? That could be a fun way to incorporate that....  so, as I said, I'm curious.)

Welcome to the Excel community.

[#4]

@Derek1974 

The comment by @mathetes triggered my curiosity. How would you lookup the value of the last cell in a column? Created a small example as can be seen in the attached workbook. Deleted some cells to break the sequence and it works as planned. Add cells towards the bottom in column B and A1 updates perfectly. The formula is:

=INDEX(B:B,AGGREGATE(14,6,(ROW(B:B)*NOT(ISBLANK(B:B))),1),1)

Perhaps this solves your problem. If not, please upload your workbook/formula as @mathetes suggested.