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)
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.
SergeiBaklan Hi, based on some of your responses I feel you can resolve this.
In my queried file, i added column with logical formula in it. then I modified some of the values in new column (calculated).
On query refresh, calculated column values which are typed gets converted to formula in the column even though it is not part of query table.
