POLACOLOCO
New Member
- Joined
- Jul 16, 2010
- Messages
- 4
Hi all,
long time no see.
Please see the file attached.
I ve used 2 milestones for ease.
Introduction:
Location - think of those as locations for an upgrade to existing building for which I need a survey and a drawing.
Milestone one - Survey visit forecast
Milestone two - Drawing submission forecast
All those milestones should have dates against them in the future to reflect when that particular job will be done.
I was trying to do a pivot on those to show something like that - PIC1.
I can do that in two pivots one for survey and another one for a drawing but I cant remember how to pivot them both or multiple other milestones into one pivot where the time frame as per PIC1 is actually the determining factor and the count is shown in the relevant columns where that criteria is met. Example for Jan 2024 where there are 4 surveys planned against 4 locations and there are 0 drawings submissions, Feb 2024 has 14 surveys one of which has a drawing submission and the other one has had the survey in January hence 14 surveys 2 drawings.
I need excel pivot to count the surveys and drawings against universal time not the months/years for the particular survey location but if the survey or drawing happens in a month then pivot counts it.
Pic1 shows the green table I created manually - I dont want to use the sumproduct or any other function to count them as I need to be able to have a pivotable table to click on the count and bring those sites in the meeting please.
Could someone help me please with regards how to run it in the pivot please?
I cant do VBA unfortunately
much obliged
PL
Data
long time no see.
Please see the file attached.
I ve used 2 milestones for ease.
Introduction:
Location - think of those as locations for an upgrade to existing building for which I need a survey and a drawing.
Milestone one - Survey visit forecast
Milestone two - Drawing submission forecast
All those milestones should have dates against them in the future to reflect when that particular job will be done.
I was trying to do a pivot on those to show something like that - PIC1.
I can do that in two pivots one for survey and another one for a drawing but I cant remember how to pivot them both or multiple other milestones into one pivot where the time frame as per PIC1 is actually the determining factor and the count is shown in the relevant columns where that criteria is met. Example for Jan 2024 where there are 4 surveys planned against 4 locations and there are 0 drawings submissions, Feb 2024 has 14 surveys one of which has a drawing submission and the other one has had the survey in January hence 14 surveys 2 drawings.
I need excel pivot to count the surveys and drawings against universal time not the months/years for the particular survey location but if the survey or drawing happens in a month then pivot counts it.
Pic1 shows the green table I created manually - I dont want to use the sumproduct or any other function to count them as I need to be able to have a pivotable table to click on the count and bring those sites in the meeting please.
Could someone help me please with regards how to run it in the pivot please?
I cant do VBA unfortunately
much obliged
PL
Data
| Location | Survey F/C | Survey Act | Drawing F/C | Drawing Act |
1932 | 30/01/2024 | 22/08/2024 | ||
1943 | 30/01/2024 | 22/08/2024 | ||
1877 | 31/01/2024 | 19/07/2024 | ||
1918 | 31/01/2024 | 22/08/2024 | ||
2152 | 06/02/2024 | 29/02/2024 | ||
2172 | 06/02/2024 | 19/04/2024 | ||
2190 | 06/02/2024 | 10/05/2024 | ||
1871 | 08/02/2024 | 26/07/2024 | ||
1884 | 09/02/2024 | 10/05/2024 | ||
1865 | 15/02/2024 | 15/08/2024 | ||
7346 | 16/02/2024 | 26/07/2024 | ||
7398 | 16/02/2024 | 26/07/2024 | ||
2259 | 16/02/2024 | 26/07/2024 | ||
1899 | 17/02/2024 | 10/05/2024 | ||
2441 | 29/02/2024 | 10/05/2024 | ||
2182 | 29/02/2024 | 19/07/2024 | ||
1889 | 29/02/2024 | 26/07/2024 | ||
1896 | 29/02/2024 | 01/01/2025 | ||
1938 | 12/03/2024 | 26/07/2024 | ||
1869 | 12/03/2024 | 26/07/2024 | ||
1965 | 12/03/2024 | 26/07/2024 | ||
1900 | 15/03/2024 | 25/06/2024 | ||
1887 | 15/03/2024 | 19/07/2024 | ||
1969 | 25/03/2024 | 22/08/2024 | ||
1870 | 28/03/2024 | 22/08/2024 | ||
7412 | 28/03/2024 | 20/09/2024 | ||
2144 | 29/03/2024 | 25/06/2024 | ||
1915 | 02/04/2024 | 21/05/2024 | ||
2168 | 02/04/2024 | 31/05/2024 | ||
1890 | 02/04/2024 | 31/05/2024 | ||
1879 | 02/04/2024 | 11/06/2024 | ||
1977 | 02/04/2024 | 26/07/2024 | ||
1959 | 02/04/2024 | 09/08/2024 | ||
7411 | 02/04/2024 | 01/01/2025 | ||
1872 | 25/04/2024 | 01/01/2025 | ||
2344 | 01/01/2025 | 01/01/2025 | ||
2289 | 01/01/2025 | 01/01/2025 | ||
1895 | 01/01/2025 | 01/01/2025 |
