!!REPOSTED!! 1 JULY 2022 @ 9:54 Formula Is Not Filling Correctly

[DropAGearN'Disappear]

Hello,

I have the following formula in a table.

=IF(B3<>"",CHOOSE(COUNTIF(B$2:B3,B3),"First Time","Second Time","Notify Supervisor!","Send To Investigations"),"")

My issue is that as more data is entered, the cell reference in the new formula on the next row changes. Based on the above formula, you'll notice in order from left to right we have B3,B2,B3,B3. When I add data to the next row it should be B4,B2,B4,B4. adding another row will then give B5,B2,B5,B5 and so on. But in some random fashion it will suddenly change the third cell reference to the last row in the table. This throws off all previous entries. For example, instead of being first time, second time, notify, send to" it will change all four to "send to".

I have attached a sample workbook so you all may see what I mean (compressed). To work the book, a number must be entered in the B column. The formula in question is in column D. When you enter the same number, it is meant to count how many times the number has been entered and return value based on the number of occurrences. I do believe it to be a auto-fill issue. Start by adding a "3" in B5 and notice how D4 suddenly changes when I dont want it to. NOTE: I have interative calculations enabled to get the timestamp to not recalculate as new data is entered; it is set to 1 and 0.001.

UPDATE: by error checking and hitting restore, it changes the formula to be correct. hmm.....

[Glenn Kennedy]

At the moment, I'm on a metered connection (expensive!!) on a campsite in France. So, I will not be downloading a monster sheet. However, on a more general basis... why do you think that setting iterations to 1 prevents changes to a datestamp? Can you PROVE that??

[DropAGearN'Disappear]

Its something I read on a forum somewhere, But no I cannot prove it. What is your knowledge of it as I haven't heard of the setting until a few days ago?

[Glenn Kennedy]

I've used that formula for years... have never changed the default no. of iterations and have never had any problems. I was just interested to see if you knew if there were any issues.

[Limor_OP]

I think it's because your data is formatted as a table and the ranges changes automatically when you add a new line to capture the whole range.
Either you remove the table formatting or try to copy first the formula when you want to open a new line and then fill in the rest of the parameters.

[DropAGearN'Disappear]

Unfortunately, I cannot expect the end user to do the re-entering, and I need the ease of a table, any other Ideas?

[Limor_OP]

Go to table---> convert to range

this will stop the auto-expand feature.

[DropAGearN'Disappear]

It does indeed, but is there any way to keep the table?