How to use a column name instead of column number to search for value

[oSmittyo]

Hello,

I'm an Excel novice to please excuse any ignorance. I'm working on a mutli-sheet workbook. On sheet1 I have the formulas to pull data off of sheet2. Currently I've been using the column letters for searching an entire column, but that prevents me from being able to shift the columns around and still pull the same data (there are around 96 columns and up to 35,000 rows, the size of the table varies month to month). When a new column is needed, I've been adding it to the end instead of inserting it where it should be so I don't break the formulas. The objective is to determine the number of times a specific text is selected in the column and count up those times.

My current formulas look like this: =COUNTIFS('Data'!$C:$C,"2Q22",'Data'!$AL:$AL,$C32)
- 'Data' is the name of the sheet I'm looking for
- There are different quarters so $C:$C is looking in this case for 2Q22, but it could be another quarter
- $AL:$AL is the column I'm searching
- $C32 is the value I'm searching for (Such as "Notice" or "I don't know")

So, instead of having $AL:$AL I'm wanting to have Excel search the table for the name of that column IE: "Smokey and the Bandit" and then count up the number of times a specific text is entered in that column IE: "Firebird" or "Semi Truck"

I've tried integrating MATCH but I have not figured how.

Any help would be appreciated.

 

[Micron]

If you don't use explicit references and you insert columns then formulas automatically adjust? So instead of
COUNTIFS('Data'!$C:$C,"2Q22",'Data'!$AL:$AL,$C32)
for example, use
COUNTIFS('Data'!C:C,"2Q22",'Data'!AL:AL,C32)
or must you use what you're using?

 

[oSmittyo]

The C column will never change so I'm not so worried about that.

If I move the column AL to column BB without changing the formula to then look in BB for the data, I won't get the results I'm looking for. Each column has it's own question as the header and answers down the rows of that column.

 

[Micron]

C isn't the point - absolute vs relative referencing is the point.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(Data!$C:$C,"2Q22",INDEX(Data!$B:$ZZ,,MATCH("Smokey and the Bandit",Data!$B$1:$ZZ$1,0)),$C32)

 

[oSmittyo]

Thank you to both Micron and Fluff! Either way may work and I have tried both (Fluff, yours took a little more testing, but did work out great in the end).

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.