Referencing dates against Products, in order to find the Qty. Without using Column referencing

[dancook79]

if i have 1 sheet which has the master data for all customers and then
i have several workbooks with customer sales

Master sheet
B2 Sales Date - this row has every day of the
year
A3 product B3 is sales Qty
1 1
2 55
3 55

Customer sheet has a similar layout but because this is made from a
pivot table its only based on sales so when it comes to christmas day
when there are no sales i cannot simply do a vlookup using column
references as my data will be out of sync

How do i reference the product and the date in excel

 

[Shane Devenshire]

Hi,

So you are using 2007? Please show us a same of the data layout in the
master and the other sheets (are they other workbooks or worksheets within
the same workbook?).

From the title of your post, but not the post itself it sounds like you want
to find a quantity for a date and products?

Suppose the Date is in B2 and the products starting in A3, the basic formula
is

=SUMPRODUCT(--(Sheet1$B$2:$NB$2=B$2),--(Sheet1$A$3:$A$100=$A3),Sheet1:$B$3:$NB$100)+...

You will need to have a similar formula after the "+" above for each of the
sheets.