How do I reference column header name from another worksheet instead of the letter

[yakhtar]

First time posting here.

I have an excel worksheet (I'll refer to it as "Data") from where I am pulling data on other sheets using Sumifs formula. Up until today I was downloading the raw data and pasting it on the "Data" worksheet. In my dashboard worksheet I am using Sumifs formula to look up data from the "Data" worksheet and put it on the dashboard worksheet.

For example, =SUMIFS(Data!$C:$C,Data!$AD:$AD) - where column "C" refers to "Revenue" and column "AD" refers to "Payment Date".

How can I use column header names (Revenue and Payment Date) instead of "C" or "AD". The reason I want to use this approach is that the columns can shift from day to day but not the header name.

 

[AlphaFrog]

Hi and welcome to the forum.

Try something like this...

=SUMIF(INDEX(Data!$A:$AZ,0,MATCH("Revenue",Data!$1:$1,0)), 
             MyCriteria,
                  INDEX(Data!$A:$AZ,0,MATCH("[COLOR=#333333]Payment Date[/COLOR]",Data!$1:$1,0)))

 

[yakhtar]

Thank you for your quick response, much appreciated. Allow me to be a little dense (relatively new to Excel). I used your formula and have some questions - what would go in place of MyCriteria? Also, I am using a cell reference to capture the date for each day to be populated. As you can see from the sample image (red circle 2) that I am capturing date in Cell F5 to populate revenue for that date. How will I be able to do this using your formula. I hope I am able to make sense.