How to Lock Table Header Reference?

Xbox_360

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
Try using the column name twice, remember the double brackets as well.
Excel Formula:
=COUNTIF(EntryTable[[header]:[header]], "word")
 

Xbox_360

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Try using the column name twice, remember the double brackets as well.
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,760
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How exactly are you moving the column? The referencing should follow the column automatically.
 

Xbox_360

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,760
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Copy and Paste doesn’t move a column. Cutting or dragging should be fine though.
 

Xbox_360

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,760
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,732
Messages
5,742,854
Members
423,759
Latest member
meb229

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top