Forum Discussion
Excel auto-changed my formula in 1st/2nd rows of table's Calculated Column
Hi Kelly,
If you delete rows from the table, let say keeping only second one, in G in formula it will be $B$2:B2. Second row it the last in the table and it's internally recognize B2 as last cell in the table in column B. When you add more rows B2 (note, that's relative reference), B2 will be change on reference in last cell in column B, in that case on B20.
In general with tables better to use structured references to avoid side effects like this. In you case formula could be
=IF(COUNTIF([Product Spec ID / Version],[@[Product Spec ID / Version]])>1,IF(COUNTIF(OFFSET(Table3[[#Headers],[Product Spec ID / Version]],1,0,ROW()-ROW(Table3[[#Headers],[USC]])),[@[Product Spec ID / Version]])=1,1,0),1)
Hi Kelly,
I think you have deleted the entire rows of the Table3 and leave only the first row of it with the formula in cell G2 like this:
I think that's happened because of the effect of the column automatically filling.
Please note that the Table3 is an Excel table, not a normal range so the formulas you use in the Excel table will automatically populate to fill the column.
The formula in cell G2 was existing before copying the rows again and the second COUNTIF has an expandable range ($B$2:B2).
When you copy the rows again into the Table3, this range is expanded to end up with last row number of the table because it was existing in the last row in the Table3 before you copy the rows again.
The formulas in other rows are correct because their rows are created again, and they are was calculated in their rows after the filling is finished.
This behavior is really weird.
I don't know if we can consider it a bug!
The solution to this issue is to use this formula in cell G2
=IF(COUNTIF($B$2:$B$20,B2)>1,IF(COUNTIF(INDIRECT("$B$2:B"&ROW()),B2)=1,1,0),1)
Instead of the fixed row number, you can use the ROW() function to always give the second COUNTIF the row number of the current row.
This is will prevent this behavior from happening.
I hope that makes sense
Regards
Thank you kindly for your explanation and suggested formula correction, Haytham! This community of tech experts is awesome.
Thank you very much :)
I don't think that the side effect is avoided because of the structured references as we can convert them to normal ranges so we get the same result:
=IF(COUNTIF($B$2:$B$20,B2)>1,IF(COUNTIF(OFFSET($B$1,1,0,ROW()-ROW($B$1)),B2)=1,1,0),1)
That's because of the OFFSET function along with the ROW().
However, this is really a good approach as the INDIRECT function may slow down the workbook if it's applied to a large number of rows.
Structured references naturally push to use OFFSET for the reference on another rows. If we use mix of structured references and absolute/relative ones - yes, we avoid nothing.
