How to Lock Table Header Reference?

[Xbox_360]

I am a pro with excel when it comes to keyboard shortcuts, otherwise I rank as a hobbyist.

My current confidential project requires fluidity and needs to be dynamic. The current problem I have run into is that I have two tables on two separate sheets. One is a data entry sheet while the other sheet references the entry data sheet to put data into graphs.

What I need is so that whenever I have columns (or headers with respective data), the references stay. I currently have set up formulas to count specific cells. The formula I use is

=COUNTIF(EntryTable[header], "word")

The header references the correct column of data of a table in another sheet and everything works, but when the column is moved, excel treats the reference as a cell reference, not a header name reference. So what happens is that I move the column in the Entry sheet and instead of reading "=COUNTIF(EntryTable[header], "word")," excel reads "=COUNTIF(EntryTable[Column1], "word")."

I know there should be some way to lock the table header reference as I can copy-paste the formula with an apostrophe and repaste it without an apostrophe to fix the problem, but this is cumbersome.

Is there a way to lock the reference so that no matter what, the formula ONLY references the table header in the reference, regardless of whether the table header is moved or not?

 

[jasonb75]

Try using the column name twice, remember the double brackets as well.

=COUNTIF(EntryTable[[header]:[header]], "word")

 

[Xbox_360]

Try using the column name twice, remember the double brackets as well.

=COUNTIF(EntryTable[[header]:[header]], "word")

Sadly that does not work, excel still references the spreadsheet column and not the actual table header, so when the column is moved in the EntryTable, the formula defaults to a blank column because the column it was referencing got renamed and it automatically renamed the reference in the formula.

So presume that Column1 is inserted and I want to move Header2 to the position of Column1
[EntryTable, Sheet1]
Header1 Header2 Header3 Column1
Data Data Data Blank
[DataTable, Sheet2]
=COUNTIF(EntryTable[Header2], "word")

What happens when I move the column and don't touch the DataTable at all is this:
[EntryTable, Sheet1]
Header1 Column1 Header3 Header2
Data Blank Data Data
[DataTable, Sheet2]
=COUNTIF(EntryTable[Column1], "word")

It automatically renames the reference in the formula. Even with your suggestion, it still renames it specific to the column.

 

[RoryA]

How exactly are you moving the column? The referencing should follow the column automatically.

 

[Xbox_360]

How exactly are you moving the column? The referencing should follow the column automatically.

The standard I have for this spreadsheet is that any column to be moved must be so via copy-paste or cut-paste. The reference for some reason only attaches itself to a column rather than an actual tag.

Could it be a bug? Just like how deleting sheet columns in a table with merged cells atop hides the entire worksheet for no reason?

 

[RoryA]

Copy and Paste doesn’t move a column. Cutting or dragging should be fine though.

 

[Xbox_360]

Copy and Paste doesn’t move a column. Cutting or dragging should be fine though.

I know Copy-Paste does not move a column, by Copy-Paste I'm implying Copy-Paste-Delete old column. But either way, cutting and dragging both result in the same issue, the table header reference acts like it's referencing a sheet column instead of the actual table header name and auto-updates to "Column1" instead of preserving the table header name in the formula.

 

[jasonb75]

I think the only way that you're going to get what you want is by using the INDIRECT function, although after reading your posts multiple times I'm not entirely sure that I'm in the right track.

 

[RoryA]

I know Copy-Paste does not move a column, by Copy-Paste I'm implying Copy-Paste-Delete old column

Which also doesn’t move a column - it creates a new one.

I can’t replicate your issue with dragging.

 

[Xbox_360]

Which also doesn’t move a column - it creates a new one.

I can’t replicate your issue with dragging.

It seems like either never tried dragging or I was not doing it right and disregarded it.

Though my issue still is an issue. I might resort to dragging, but I must know if it is possible to lock references or not without dragging. Attached to this email is a gif of the problem. The mouse will be off-centered due to recording software, but otherwise it showcases the problem I was trying to solve.