Formula / array to convert cell format for entire range to be used with Sumproduct

[jbrouse]

Good morning.

I have a spreadsheet - "Orders" - that gives me daily production forecast for the next 3 months. I have created a separate tab that sums this data by month. The headers for the production forecast come in the format of YYYYMMDD while the headers on my monthly summary tab are, for example, "June". I have currently matched this data up by copying and pasting a formula

=TEXT(DATE(LEFT(B4,4),RIGHT(LEFT(B4,6),2),RIGHT(B4,2)),"mmmm")

above each column in the production forecast tab to convert the date format into a "month" text string. On my monthly summary tab, I use the following SUMPRODUCT to pull the data.

=SUMPRODUCT((SalesPNLookup=A1)*(SalesMonthLookup=A2),SalesData),

where SalesPNLookup is on sheet 'Orders', range A5:A10000
SalesMonthLookup is on sheet 'Orders', range B4:BR4
And SalesData is on sheet 'Orders', range B5:BR10000

This formula works well. However, the end users know nothing about Excel and formulas, so I do not want them to have to add the formula to the 'Orders' worksheet to make it work. Is there any way through named formulas or other that I can make this so that the end user simply copies and pastes the data into the worksheet and the calculations on the summary tab work?

I appreciate any help that can be offered.

Thanks in advance!