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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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.
Disregard my last comment on dragging. Dragging worked with the small-scale tables, with the document I'm working with dragging does not work, it still references the column instead of the table header name.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,816
Office Version
  1. 365
Platform
  1. Windows
As I already mentioned in post 8
the only way that you're going to get what you want is by using the INDIRECT function
Excel Formula:
=COUNTIF(INDIRECT("EntryTable[header]"), "word")
Not an ideal solution, but with what you're attempting I'm not sure that such a thing exists.
 
Solution

Xbox_360

New Member
Joined
Jul 28, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
As I already mentioned in post 8

Excel Formula:
=COUNTIF(INDIRECT("EntryTable[header]"), "word")
Not an ideal solution, but with what you're attempting I'm not sure that such a thing exists.
I dunno, seems like a pretty ideal solution to me considering this was the exact behavior I was looking for.


Thanks for the help and writing out the formula, I never knew of the INDIRECT function before.
 

Forum statistics

Threads
1,143,909
Messages
5,721,461
Members
422,363
Latest member
Bogus_Potatoes

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