B
Brian
Here is the situation. I have a number of members in a clay target
club who shoot a competition over a number of ranges. Ranges 1 to 8.
They shoot a competition over 4 days. They start shooting at a
specific time each day. Start time in cell A1. The duration of the
time they spend on each range is specified in B1. These times may
vary each day.
I have set up a table in the worksheet that shows the squad numbers in
column A, the ranges they shoot each day and the time they start to
shoot on each range.
This table only shows the squad numbers up to the number of members
shooting, which is done on a seperate worksheet (Squadding
Worksheet)where all entrants are placed into squads. I used the
"large" formula to only show up to the last squad number reflected on
the squadding worksheet, else "blank". On day 1 the first 8 squads
shoot the first 8 ranges, followed by the next 8 squads. The first 8
squads then shoot the next 8 ranges (Squad 1 shot range 1, they now
shoot range 2. Squad 2 shot range 2, they now shoot range 3, etc)
Each squad only shoots 2 ranges a day so once the first 16 squads are
finished. The next 16 squads start.
I have no problem doing the spreadsheets and formulae for day 1 and 3.
This works perfectly. The problem is that on day 2 and 4, the squads
shoot in reverse order with the last squad on day 1 shooting as the
first on day 2. Here is my problem. Until the last moment I do not
know how many squads will enter the competition, and it will not be a
multiple of 8. I could have 35 squads. On day 1 we would put through
16 squads, then another 16 and then 3. On day 2 the last 3 squads
start,plus the previous 13 (total 16)
I need a formula I can put into the column for day 2 which will
calculate the time each squad starts. (The range they shoot is no
problem, this is done) Remember the last squad is at the bottom of
the table but I do not know how many squads there will be.
If anyone can help, I can mail the workbook if needed.
club who shoot a competition over a number of ranges. Ranges 1 to 8.
They shoot a competition over 4 days. They start shooting at a
specific time each day. Start time in cell A1. The duration of the
time they spend on each range is specified in B1. These times may
vary each day.
I have set up a table in the worksheet that shows the squad numbers in
column A, the ranges they shoot each day and the time they start to
shoot on each range.
This table only shows the squad numbers up to the number of members
shooting, which is done on a seperate worksheet (Squadding
Worksheet)where all entrants are placed into squads. I used the
"large" formula to only show up to the last squad number reflected on
the squadding worksheet, else "blank". On day 1 the first 8 squads
shoot the first 8 ranges, followed by the next 8 squads. The first 8
squads then shoot the next 8 ranges (Squad 1 shot range 1, they now
shoot range 2. Squad 2 shot range 2, they now shoot range 3, etc)
Each squad only shoots 2 ranges a day so once the first 16 squads are
finished. The next 16 squads start.
I have no problem doing the spreadsheets and formulae for day 1 and 3.
This works perfectly. The problem is that on day 2 and 4, the squads
shoot in reverse order with the last squad on day 1 shooting as the
first on day 2. Here is my problem. Until the last moment I do not
know how many squads will enter the competition, and it will not be a
multiple of 8. I could have 35 squads. On day 1 we would put through
16 squads, then another 16 and then 3. On day 2 the last 3 squads
start,plus the previous 13 (total 16)
I need a formula I can put into the column for day 2 which will
calculate the time each squad starts. (The range they shoot is no
problem, this is done) Remember the last squad is at the bottom of
the table but I do not know how many squads there will be.
If anyone can help, I can mail the workbook if needed.