Can you reference "relative" column heading names in a formula?

[trishcollins]

I have a table that uses the countifs formula in each cell, that references the contents of the table column heading name and the data in the first column as criteria and counts up items in the second table (Deliverable Assignments) if they match both.

I am currently using the absolute row and relative column cell address for the heading rather than the actual table header name, ie. "[[#headers][Guideline]]" for two reasons. The first is that I want to copy the formula to all subsequent columns, and don't want to edit each column to reflect the new heading name for that column, and the second is the column titles will change over time and I don't want to have to keep changing the formulas. I also want to copy the table to other locations, and want to ability to be able use it without having to change the formulas.

Here is the current formula (note that for the heading, I am looking for the text string to be contained within the column in the other table, not an exact match, ie "IP Addressing Guideline" would be counted.

=COUNTIFS(Deliverable_Assignments[Status],[@Status],Deliverable_Assignments[Deliverable],"*"&B$24&"*")

How to I reference the contents of the header of the column relative to the cell column the formula is in?

Trish

 

[trishcollins]

RESOLVED Re: Can you reference "relative" column heading names in a formula?

I figured it out. Was even able to include the "title" which was also counting criteria.

=COUNTIFS(Deliverable_Assignments[Section],INDIRECT(ADDRESS(ROW(EA_Telecom[#Headers])-1,COLUMN(EA_Telecom[#Headers]))),Deliverable_Assignments[Status],[@Status],Deliverable_Assignments[Deliverable],"*"&INDIRECT(ADDRESS(ROW(EA_Telecom[#Headers]),COLUMN()))&"*")

Trish