H
HAJ
Hello everyone,
I need to create a "League Ladder" which will calculate how many hours a
volunteer has worked at a particular venue, but will also summarise how
many people and/or hours from various clubs and the club regions have
been contributed.
The column titles will include:-
Date (dd/mm/yyyy format)
Region (Southern, Western, Eastern, Northen, Rural, Schools)
Club
Surname
Given Names
Status (Adult, Child)
Time Start
Time End
Total Time
and assorted other fields for additional data
I need to create a League Ladder that will look something like this:-
Region Club Surname Given Names Status Hours Total
Attendances
Eastern Melbourne SMITH John Adult 125:30 25
I will probably be entering data for all events into one single
consolidated sheet, then extracting information into separate sheets for
each event for "at a glance" daily information. I will also use a
summary sheet for miscellaneous data required for reporting to
headquarters on a weekly basis.
Names and Clubs and Regions are irregular in attendance, so I cannot
work on the basis that every person will be at every event, or that
every Club or every Region will be represented at any given time.
We normally have b/w 15 and 25 people at anyone event, but it can reach
up to 60 or so on big days, depending on the weather, other commitments
and the anticipated crowd size of the particular event to be attended.
Does anyone have any suggestions on how to set this up most efficiently?
I've tinkered with a few ideas, but none seem to be completely suitable,
and I'm confronted with having to create some extremely difficult
formulas if I'm to achieve some of the things I want.
The CountIf function is limited to one piece of data at a time, such as
CountIf(A3:A75,"Eastern"). This has to be repeated for each Region and
isn't too difficult.
However, within the half-dozen Regions we have up to 50 Clubs, and each
club can have b/w a dozen and 60 members, so the permutations are
enormous.
I could possibly use a Member Number to do a lot of the checking; this
will save a fair bit of formula re-construction in each separate case,
but it will not add the data on their Region and Club into the adjacent
results fields.
Is it possible to do this sort of thing in Excel, or should I do it in
Access and dump the data into Excel instead?
Any suggestions, advice etc will be very welcome and if you need more
information, you are welcome to ask for it.
Cheers,
H in Melbourne, Victoria
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
I need to create a "League Ladder" which will calculate how many hours a
volunteer has worked at a particular venue, but will also summarise how
many people and/or hours from various clubs and the club regions have
been contributed.
The column titles will include:-
Date (dd/mm/yyyy format)
Region (Southern, Western, Eastern, Northen, Rural, Schools)
Club
Surname
Given Names
Status (Adult, Child)
Time Start
Time End
Total Time
and assorted other fields for additional data
I need to create a League Ladder that will look something like this:-
Region Club Surname Given Names Status Hours Total
Attendances
Eastern Melbourne SMITH John Adult 125:30 25
I will probably be entering data for all events into one single
consolidated sheet, then extracting information into separate sheets for
each event for "at a glance" daily information. I will also use a
summary sheet for miscellaneous data required for reporting to
headquarters on a weekly basis.
Names and Clubs and Regions are irregular in attendance, so I cannot
work on the basis that every person will be at every event, or that
every Club or every Region will be represented at any given time.
We normally have b/w 15 and 25 people at anyone event, but it can reach
up to 60 or so on big days, depending on the weather, other commitments
and the anticipated crowd size of the particular event to be attended.
Does anyone have any suggestions on how to set this up most efficiently?
I've tinkered with a few ideas, but none seem to be completely suitable,
and I'm confronted with having to create some extremely difficult
formulas if I'm to achieve some of the things I want.
The CountIf function is limited to one piece of data at a time, such as
CountIf(A3:A75,"Eastern"). This has to be repeated for each Region and
isn't too difficult.
However, within the half-dozen Regions we have up to 50 Clubs, and each
club can have b/w a dozen and 60 members, so the permutations are
enormous.
I could possibly use a Member Number to do a lot of the checking; this
will save a fair bit of formula re-construction in each separate case,
but it will not add the data on their Region and Club into the adjacent
results fields.
Is it possible to do this sort of thing in Excel, or should I do it in
Access and dump the data into Excel instead?
Any suggestions, advice etc will be very welcome and if you need more
information, you are welcome to ask for it.
Cheers,
H in Melbourne, Victoria
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***