JosephGiri
New Member
- Joined
- Jan 15, 2014
- Messages
- 3
Hi everyone,
I have a leave application data entry table that looks like this,
SheetName: Data
I have a separate sheet that has the following structure,
SheetName: Counter
I need to create a non-VBA solution to populate column B in Counter.
A sample set of data is shown below,
SheetName:Data
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
My expected result (only showing for Jan) is,
ShhetName: Counter
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I achieved this by "brute force", as follows,
There must be a more elegant solution as the above approach takes time to recalculate after each set of entries.
Any help would be appreciated.
Thank you.
I have a leave application data entry table that looks like this,
SheetName: Data
A: Name
B: Start Date
C: End Date
B: Start Date
C: End Date
I have a separate sheet that has the following structure,
SheetName: Counter
A: Date (from 01 Jan to 31 Dec)
B: No of People on Leave
B: No of People on Leave
I need to create a non-VBA solution to populate column B in Counter.
A sample set of data is shown below,
SheetName:Data
A | B | C | |
1 | Name | Start Date | Return Date |
2 | P1 | 1-Jan | 6-Jan |
3 | P2 | 1-Jan | 7-Jan |
4 | P3 | 1-Jan | 12-Jan |
5 | P4 | 4-Jan | 5-Jan |
6 | P5 | 6-Jan | 10-Jan |
7 | P6 | 10-Jan | 12-Jan |
8 | P7 | 13-Jan | 18-Jan |
9 | P8 | 19-Jan | 21-Jan |
10 | P9 | 26-Jan | 9-Feb |
11 | P10 | 28-Jan | 1-Feb |
12 | P11 | 29-Jan | 2-Feb |
13 | P12 | 30-Jan | 6-Feb |
14 | P13 | 30-Jan | 1-Feb |
15 | P14 | 30-Jan | 3-Feb |
16 | P15 | 30-Jan | 1-Feb |
17 | P16 | 31-Jan | 5-Feb |
18 | P17 | 28-Feb | 9-Mar |
19 | P18 | 18-Apr | 23-Apr |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
My expected result (only showing for Jan) is,
ShhetName: Counter
A | B | |
1 | Date | Number of Ppl on Leave |
2 | 01 Jan | 3 |
3 | 02 Jan | 3 |
4 | 03 Jan | 3 |
5 | 04 Jan | 4 |
6 | 05 Jan | 3 |
7 | 06 Jan | 3 |
8 | 07 Jan | 2 |
9 | 08 Jan | 2 |
10 | 09 Jan | 2 |
11 | 10 Jan | 2 |
12 | 11 Jan | 2 |
13 | 12 Jan | 0 |
14 | 13 Jan | 1 |
15 | 14 Jan | 1 |
16 | 15 Jan | 1 |
17 | 16 Jan | 1 |
18 | 17 Jan | 1 |
19 | 18 Jan | 0 |
20 | 19 Jan | 1 |
21 | 20 Jan | 1 |
22 | 21 Jan | 0 |
23 | 22 Jan | 0 |
24 | 23 Jan | 0 |
25 | 24 Jan | 0 |
26 | 25 Jan | 0 |
27 | 26 Jan | 1 |
28 | 27 Jan | 1 |
29 | 28 Jan | 2 |
30 | 29 Jan | 3 |
31 | 30 Jan | 7 |
32 | 31 Jan | 8 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I achieved this by "brute force", as follows,
- creating a 31 column range with the individual dates as the column headers
- use a formula to check if the date in the header is in between Start Date and End Date
- count the number of "TRUE" cases found for each date
- Transpose into a new sheet for display purposes
There must be a more elegant solution as the above approach takes time to recalculate after each set of entries.
Any help would be appreciated.
Thank you.