C
capt
Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.
A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....
There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):
Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)
Point to note: "21:00 hours is a total of both clients hours.
To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:
Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12
Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12
My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.
I would greatly appreciate any help on this.
Thank you
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.
A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....
There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):
Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)
Point to note: "21:00 hours is a total of both clients hours.
To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:
Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12
Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12
My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.
I would greatly appreciate any help on this.
Thank you