Changing the [@column] column number within a table

[sgcannon]

I am a little confused about why excel assigns wacky numbers to the columns within a table. For example, I have [@column101] then [@column102] and then [@column1022]. I can only figure this is because I inserted a column after 102 and it is therefore designated 1022.

My problem isn't why it does this (though I'm curious) but how to I change those column numbers. I have multiple pages, each with a different table. Each table has a section that is common to the other tables on the other pages. It would be super handy if those sections all had the same [@column] numbers. I often tweak a formula on one page and I'd like to copy that formula over to the corresponding sections on other pages. I can cut and past the formula but on the other pages, the columns all have different numbers so I have to manually go through and change of the [@column] references.

For example, on page 1 I have:
=IF([@Column82]=0,"",(150*([@Column62]-2)+IF([@Column62]=3,50,0)+20*(CODE([@Column7])-64)+(2*[@Column82]-100)+IF([@Column102]="Avoid",-100*([@Column10]-2.5))+(50-[@Column9])/2)-20*[@Column10]-4*[@Column103]-20*[@Column10223])

But on page 3 I have:
=IF([@Column24]=0,"",(150*([@Column22]-2)+IF([@Column23]=3,50,0)+20*(CODE([@Column23])-64)+(2*[@Column24]-100)+IF([@Column29]="Avoid",-100*([@Column26]-2.5))+(50-[@Column25])/2)-20*[@Column26]-4*[@Column28]-20*[@Column31])

It is the exact same formula but I had to manually go in and change all the column references.

Thanks!!!

Steve

 

[Edgar_]

Column names in list object tables are meant to be unique so you can easily reference them while reading and building formulas. Excel assigns wacky numbers if you add a column whose name already exist. If you have two or more tables that should have the same column names, just copy the names in one and paste them in the other.

 

[sgcannon]

Column names in list object tables are meant to be unique so you can easily reference them while reading and building formulas. Excel assigns wacky numbers if you add a column whose name already exist. If you have two or more tables that should have the same column names, just copy the names in one and paste them in the other.

That actually worked!!!