Forum Discussion

Kelly Evenson's avatar
Kelly Evenson
Copper Contributor
Nov 30, 2018
Solved

Excel auto-changed my formula in 1st/2nd rows of table's Calculated Column

Hello all, OK ... this problem has me completely stumped. Background: I have a report that is created in two steps. The first is a data extract out of the company's PLM system, which generates a s...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 30, 2018

    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)

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 01, 2018

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.

Ravitosh Kumar's avatar
Ravitosh Kumar
Copper Contributor
Dec 03, 2019

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.