Recognise only 1 change to cell (Ignore any future changes) using IF Formula

[brianqman]

Hi all,
I am trying to make a cell only recognise 1 change to it (using the If Formula) and ignore any other changes but don't know how to modify the if formula to do this. See linked file here https://1drv.ms/x/s!AnSBTt3wJpj-5hBle6pTJ6tVZUW5
Please see TODO in the spreadsheet which shows what I need.
Regards
Brian

 

[Fishboy]

Hi all,
I am trying to make a cell only recognise 1 change to it (using the If Formula) and ignore any other changes but don't know how to modify the if formula to do this. See linked file here https://1drv.ms/x/s!AnSBTt3wJpj-5hBle6pTJ6tVZUW5
Please see TODO in the spreadsheet which shows what I need.
Regards
Brian

Hi brianqman, welcome to the boards.

Based on your description and the instructions in the linked workbook I do not think that can be done with formulas alone. It looks like it would most likely require some kind of VBA coding to achieve that goal.

As a lot of forum members will not download files from file hosting sites (or at least usually not from new members) I have included the summary of what you included in the document here:

If cell B5 changes to awake at any time in a 24 hour period, put number 1 otherwise put number 2.
Forget all other changes (awake 2 or 3 or 4 times etc) as we only want the formula to work for
first asleep to awake change.
So formula for C5 would normally be: =IF(B5="awake","1","2") however this won't work when
changing multiple times. So I need it to be something like =IF(B5="asleep"(1timeonly),"1","2")
In other words, lock the cell or prevent it from recognising any other changes once 1 change from
asleep to awake has occurred. Ignore all other changes for 24 hours.

 

[brianqman]

Thank you for the reply.
If I can't do it within the if formula how do I do it within VBA (so that it only recognises 1 change to a cell's data - ignores 2 or more changes)?

 

[Fishboy]

Thank you for the reply.
If I can't do it within the if formula how do I do it within VBA (so that it only recognises 1 change to a cell's data - ignores 2 or more changes)?

Is the value in B5 going to be manually changed to Awake or as the result of another formula?

 

[brianqman]

It will be changed automatically based off A5 (TIME).