Match Date and Autimatically Add to Another Sheet

[NJ007]

Hi

I would be glad if someone can assist me with the following query.

Everyday I receive products that could be categorised into one of the five categories A, B, C, D and E. I enter these on a sheet (SHEET 1) along with an expected date of shipment, which could be any date between now and 12 months later.

In another sheet (SHEET 2), I have months in column A and each product type for each month in column B.

What I want is to write a formula in SHEET 2 so that every time an entry is made in SHEET 1, it automatically adds 1 to the respective cell in SHEET 2. For example, I received 10 products today (5*A, 2*B, 1*C, 1*D and 1*E). As soon as i made entry in SHEET 1, I wanted respective numbers to appear in SHEET 2.

Can anyone help?

Please see attached example.

Thanks
NJ007

[ajryan88]

Hi,

Please see the attached workbook. I have inserted a helper column, as dealing with merged cells is a pain. So column A contains the month, but on every row for use in my formula, which is in column E. I have left your column D untouched so that you are able to verify that the results produced are accurate.

I hope this helps

[MarvinP]

Hi NJ007,

I'd do this problem using a Pivot Table and count the A - E. It is something like the attached where the table grows with each new line using Dynamic Named Ranges (DNR) and an event macro to automatically update the pivot whenever a new row is added. See the attached for the idea.

[NJ007]

Awesome Thanks!!!

[ajryan88]

No worries!

Please don't forget to mark this thread as solved, and please click on the * next to any post that has been helpful to say thanks to the poster