Previous Topic Next Topic

Excel help formula which capture this as Prod

Closed
jai - Mar 5, 2011 at 04:07 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Mar 5, 2011 at 09:10 AM
Hello,

Can you please help me on this:

The below data is in sheet 1:

Products DATES VALUE
A 2-Jan 1
B 2-Jan 1
C 2-Jan 2
D 2-Jan 1
A 3-Jan 1
B 3-Jan 1
D 3-Jan 1
D 4-Jan 1
C 4-Jan 1
A 4-Jan 1
A 4-Jan 1
B 4-Jan 1

I sheet 2, i require a formula which capture this as Products to be captued in the column, date in row and the value as sum. The output should be as:

Eg
2/JAN 3/JAN 4/JAN
A 1 1 2
B 1 1 1
C 2 1
D 1 1 1

Sheet 1 had values on 4/ Jan as : A 4-Jan 1
A 4-Jan 1

while sheet 2 should show as:
4/JAN
A 2


PLease suggest. I am using excel 2007


1 reply

Answer 1 / 1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Mar 5, 2011 at 09:10 AM
Most probably you would need a macro to do that

General idea would be on sheet 1, use the event
Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

this you can use to see if a new date has been entered on a sheet, then write that date to the sheet 2

also you can update the product information on sheet2 as things are changed in sheet 1.

I am not sure if it is doable via formula and even if it can be, it would be a memory hog and make your workbook as slow as my pay raise (ok may be a little faster than my pay raise but still it would be slow)
0