need help using header reference to pull data from worksheet

Rob__James 

If you have Office 365 there are now other approaches to assembling data from multiple sheets.  If the data is held within a 3D range then VSTACK will append the datasets.  From there one would need to separate attribute names from the value columns and arrange to taste.

Calling the 3D range ='20231118:20231202'!$A$2:$B$4 by the name data

= LET(
    stacked, VSTACK(data),
    values,  TAKE(stacked,,-1),
    WRAPCOLS(values, 3)
  )

returns the value array, whilst

= LET(
    stacked, VSTACK(data),
    UNIQUE(TAKE(stacked,,1))
  )

provides a list of attribute names.

These could be combined into one formula and the result presented as a Lambda function

APPENDHλ(data)

= LET(
    stacked, VSTACK(data),
    items,   UNIQUE(TAKE(stacked, , 1)),
    values,  TAKE(stacked, , -1),
    HSTACK(items, WRAPCOLS(values, 3))
  )

To return column headers from the names on the sheet tabs, it is common practice to extract the sheet name to cell A1 using the formula 

= TEXTAFTER(CELL("filename", A1), "]")

and then, identifying that as a 3D range sheet

= TEXT(VALUE(TOROW(sheet)), "0000-00-00")

returns column headers in a slightly more readable format.

Rob__James I would use power query to automate that process. I was able to generate the table below.

Rob__James 

You can use the INDIRECT function for this purpose. Since I don't know what the sheets for the individual dates look like, I'll provide an example. In B3:

=SUMIFS(INDIRECT("'"&B$2&"'!$F$1000"), INDIRECT("'"&B$2&"'!$A$1000"), $A3)

This sums the quantities in column F on the other sheet for the rows where column A contains the product in A3 ("Nails").

This can be filled down and to the right.