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?

 

[Xbox_360]

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

I can’t replicate your issue with dragging.

Sorry, not email, but forum message.
Here is a back-up link to onedrive for the gif.

OneDrive

1drv.ms

 

[Xbox_360]

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]

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

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

Not an ideal solution, but with what you're attempting I'm not sure that such a thing exists.

 

[Xbox_360]

As I already mentioned in post 8

=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.

OneDrive

1drv.ms

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