Update formula to include cells?

[rizzo93]

I have a column of dates that will be added to over time and include duplicates. I need to extract unique dates from that column to create a dynamic list.

To that end, I created a helper column (Column F) to display a 1 for when each date first occurs in the column; if not, then 0.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]Tuesday, November 27, 2018
[/TD]
[TD] 1

[/TD]
[/TR]
[TR]
[TD]Monday, November 26, 20181
[/TD]
[TD] 1

[/TD]
[/TR]
[TR]
[TD]Friday, November 26, 2018
[/TD]
[TD] 0

[/TD]
[/TR]
[TR]
[TD]Friday, December 7, 2018
[/TD]
[TD] 1

[/TD]
[/TR]
[TR]
[TD]Friday, December 7, 2018
[/TD]
[TD] 0

[/TD]
[/TR]
[TR]
[TD]Friday, December 8, 2018
[/TD]
[TD] 1

[/TD]
[/TR]
</tbody>[/TABLE]

The formula in column F:

 =IF(A2="","",(IF(COUNTIF(A$2:A2,A2)=1,1,0)))

The COUNTIF uses an expanding range (A$2:A2, A$2:A3, etc.)) to determine whether or not the date in column A is the first one.


For some reason, Excel display one of those in-cell error tips (the ones with the green arrow) stating "formula omits adjacent cells." This always starts in the second cell in column F. If I select "Update Formula to Include Cells" from the pop-up tip, the error will the change to "Copy Formula from Above".


This will throw off my calculations for other columns.

Anyone know what's going on?

 

[Johnny C]

You could just turn that error off in Error checking if it's bothering you

 

[rizzo93]

Hi JohnnyC, and thanks.


Ordinarily, that's what I'd do, but this is a case where it's messing up my calculations...at least, up to this point.


I've since tried entering the new data consistently which seems to have kept the problem from happening for now.