I have 3 separate excel sheets-
(1) a list of invoices with columns: Invoice Number, customer ID, customer Name, and Amount Due.
(2) a list of products of a certain type- it has columns: invoice number, and date on it (no amount due or customer identification)
(3) a customer listing with various monitoring items that are filled in primarily from sheet (1)
I would like to add a monitoring item/column in sheet (3) that looks, by customer, for the $ amount of invoices on list (2) older than 30 days How would I go about this in excel?
So- for example, if I had on sheet (1)
invNo custName custNo amt
0001 ABC Co C1234 $20
0002 ABC Co C1234 $15
0003 ABC Co C1234 $50
And on sheet (2) I had two entries (one less than 30 days and one over 30 days)
invNo prodDate
0001 9/12/2019
0003 4/4/2018
I'd simply like a formula to get sheet (3) to fill in productInvoicesOver30 and productInvoices (total of invoices on sheet (2))
custNo productInvoices productInvoicesOver30
C1234 $70 $50
C5678 $0 $0
There may be items on sheet (2) with no date at all
I'm a bit stumped trying to find a way to do this that doesn't require altering sheets (1) or (2), so any help would be greatly appreciated!