Creating Report from Working list

[Adam72]

Hi

I have my working list (sheet1) with column headings of Priority, Name, Brief, Due Date, Dept, Description, % Completed and Date Completed. Whilst I filter this while assigning tasks i need to create a report to show all the peoples incomplete work, to print out on 1 page for a meeting.

Enter Sheet 2 where cell A2 has the persons Name as header and A319 for the data [priority, brief, % Complete and Due Date], E2 for next name header and E3:H19 for data etc. The next line of Names starts A20 with A2137 for data.

How do i code a macro to get Excel to look at the name and retrieve only the incomplete items (using Date Complete is null), paste the list into the designated cells and sort by Due Date in descending order?

Assumptions:
The Name headers in report are static, no dynamic control required.
There will not be more than 15 items assigned to any person at any given time.

Appreciate any help !

Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[SkipVought]

Hi,

Please post some representative sample data and then the result you want to see, based on that data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Adam72]

Hi Skip

I have uploaded a copy of the file (easier than me trying to type in a sample) I am using with some temp data in there. I need the macro to take the data from the workplan list and push it to the workplan allocation report.

Thanks

Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[SkipVought]

"easier than me trying to type in a sample"

What about COPY 'n' PASTE???

Some of us have company imposed security restrictions that prevent us from accessing data on the www.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Adam72]

hmmm didn't seem to work last time I tried so here goes....


Priority Name Brief Due Dates For Description % Complete Completed
1 Adam Key Dates 1/08/2010 EIM Calendar of key dates to create understanding of scheduled data requirements 40%
1 Adam Annual Review 15/08/2010 EIM Facilitate the Annual Review Documents 80%
1 Adam C7 Decommision 1/12/2010 EIM Supervise the conversion of required data and the decommissioning of C7 10%
1 Benny Finance Exec Pack 30/06/2010 FBSC Assist Tracy with developing the Finance Exec Pack in Report Author 25%
1 Betty GPS Workload Ongoing GPS Assist with understanding of the GPS workload requirements
1 Betty Dashboard (International) 1/09/2010 ECUI Prepare Dashboard with International Focus 0%

and take this to something like....

Adam Betty Tracy Nick
2 Dashboards 25/06/2010 50% 1 GPS Workload Ongoing 1 Finance Exec Pack 30/06/2010 2 HDR 1/08/2010
1 Key Dates 1/08/2010 40% 1 Dashboard (International) 1/09/2010 0% 3 Dashboard (Finance) 3 Integrated reporting
1 Annual Review 15/08/2010 80% 1 School Review 1/10/2010 10% 3 Integrated reporting 2 User Forum Prep Ongoing
2 Uni Forums 1/11/2010 2 Faculty Annual Review 15/08/2010 0.8 2 Training Dev Ongoing 1 BA Reporting packs 1/09/2010
2 VCPMG 1/11/2010 3 1st Line Support Ongoing 3 WAAPA Board Report
1 C7 Decommision 1/12/2010
3 GPS Ongoing
3 Data Changes Impact Analysis

Thanks.









Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[SkipVought]

Adam,

First of all, your example does not contain consistent data. If you want some help, ya gotta help yourself by probiding good clear consistent information.

1. Name your column ranges in your source data faq68-1331

2. Seems you want data under each name from FOUR columns. YES?

Here's the drill.

1. Use the Advanced Filter to generate a unique list of Names.
2. Copy the list and Edit > Paste Special -- TRANSPOSE on the sheet for your report.
3. COPY that horizontal list and PASTE in the next adjacent cell to the right on the same row THREE more times, so that you have FOUR occurrence of each name on that row.
4. SORT on ROW 1 horizontally, so that all FOUR occurrences of each hname are contiguous.
5. Enter the four headings for the columns you want in your report in the next row under the four occurrences of the first name, AS THEY APPEAR IN THE NAME BOX. Note that the Named Range for % Complete is merely Complete and note the UNDERSCORE in Due_Dates.
6. COPY these four values across, under each group of names.

The formula, starting in A3:
[tt]
A3: =INDEX(OFFSET(Sheet1!$A$1,MATCH(A$1,Name,0),MATCH(A$2,Sheet1!$1:$1,0)-1,COUNTIF(Name,A$1),1),ROW()-2,1)
[/tt]
use conditional formatting to hide #REF! errors.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, Here's what my results are for THREE headings for 2 names...
[tt]
Adam Adam Adam Benny Benny Benny
Priority Brief Due Dates Priority Brief Due Dates
1 Key Dates 8/1/2010 1 Finance Exec Pack 6/30/2010
1 Annual Review 8/15/2010
1 C7 Decommision 12/1/2010
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Adam72]

Hi Skip

yeah just got into work logged in and realised the inconsistencies in what I pasted.... sorry. It was late when I did that.

Thanks will give this a go today.

Cheers

Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[Adam72]

Hi Skip

Have followed your steps (the dynamic name ranges is great thanks) and the information comes through to the sheet. Appreciate your assistance and guidance.

I have 2 questions though:
1. I still need filtering for "Completed" (date field) is null. Would you add the filter in the formula or just use auto filters on the result?

2. I have tried to adapt this method to create a monthly timeline so data is:

Brief[tab][tab][tab] Due_Dates
Key Dates[tab] 01/08/2010 <being 1st August>
HDR[tab][tab] [tab] 25/08/2010
Dashboard[tab] 01/09/2010
School Review 14/10/2010

and i get to see:

Aug-10[tab][tab][tab]Sep-10[tab][tab][tab]Oct-10
brief[tab][tab][tab][tab]brief[tab][tab][tab][tab]brief
Key Dates[tab][tab]Dashboard[tab][tab]School Review
HDR

but of course it is looking for the first of every month as Aug-10 is stored as 01/08/10 so only returns Key Dates and Dashboard in the timeline.

I have tried to use the date(year(), month(),1) but this didn't work. Any advice?

Thanks

Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[SkipVought]

[tt]
=date(year(DateRef), month(DateRef),1)
[/tt]
will return the FIRST of the month referenced by DateRef. This returns the LAST of the month....
[tt]
=date(year(DateRef), month(DateRef)+1,0)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}