Relative Column Referencing in a Structured Table

[sfpowell]

I was helping a colleague build a formula inside of a structured table. Part of the formula included a reference to a cell in a different column in the same row like [@[SiteID]]. When we copied that formula to one column over to the right, still in the same table, the reference of [@[SiteID]] changed to one column over to the right of it like [@[Address]]. Throughout the formula it acted the same as if we used relative referencing in a regular formula. It's as we we had the formula '=A1' in cell C1 and then copied it to D1 to result in '=B1'. I didn't even know that Excel could do relative referencing within a structured table. How did that happen?

 

[sfpowell]

I figured it out. It's the difference between copy and paste and clicking the bottom right corner of the cell and dragging it over. I always do a 'Ctrl-C' to copy, then highlight the range I'm copying to, and hit 'Ctrl-V' to paste. That works as I expected it to with the column names acting as absolute references. Apparently, if you click the box in the bottom right corner of the cell and drag it across it acts as if all the column names are relative references and changes the formula accordingly.

 

[dreid1011]

You can also paste directly into the formula bar and it will paste the literal value of the copy without updating any cell references.