Dynamic data summary sheet?

[pilgrimpez]

Hi all

I'm brand-new to this forum and I'm very pleased and grateful to find such a willing band of capable volunteers - I've been reading through some of the past posts and the responses are truly amazing!

I'm not new to Excel and I know my way around using many of the simpler formulas and data formatting/manipulation methods, but I don't know VB or macros.

I have a problem that I've been searching through those posts for an answer to, but haven't yet been able, so I wonder if someone could advise, please?

I have a list of tasks and actions. The first column contains an action identifer (text), which is a fixed three-digit code depending on the department, with a five-digit number appended to it. Then there's another column that denotes the type of action (a single text character code from a possible list of five or so - examples are 'D' for 'Write a document', 'C' for 'Set up conference call' etc, that kind of thing). Then there's a person's name.

The number of rows in the list is not constant and can vary considerably at any point in time from around five hundred to six or seven hundred or more. Entries are added and deleted all the time .

In another sheet I need to create a vertical summary list of all the people who are named in the actions - there will be many repeated names as there will only typically be around 10-15 people ever named in the list, but I only want their names to appear once in the summary. I need them in alpha sorted order too. To the right of each entry in this list, I want a row of cells with a header of all the three-digit department codes used in column 1 of the main sheet. I know these codes in advance so I don't need to do anything fancy here to populate that header, I can just type them in. If that person is doing work for that department, I want 'Y' in the appropriate column - so if the first three digits of the action id match those in the sub-table in the summary list, basically.

I think I can do some of this with a pivot table? But I've no idea how..

Lastly, I mentioned the action type column in the main sheet - I want to restrict the summary to only select one particular action type, for example. only those with 'D'. (This is the main bit I'm having problems with when I experimented with pivot tables. If possible, I need it all to auto-update too when new rows are added or removed from the main list, or if people's names change in the existing rows.

I want to do all of this without using any background code or macros, if possible, just in-sheet formulas.

This is for Excel 2010, on Windows 7 and XP.

Many thanks in advance

PP

 

[Mohammad Basem]

Hi and welcome to the forum!

Posting sample data along with sample results showing the data and layout help members to clearly understand the needs and come-up with solution/advice.

To post a file, upload it to any file hosting site and post the link.

Good luck!

 

[pilgrimpez]

Hi Mohammad

That's a good idea - thanks..!

I'll try to illustrate it in-stream here.

So my 'base' sheet looks something like this:

ID	Type	Assignee
ACC00001	D	Joe Brown
FIN00001	D	Mabel Smith
PER00012	I	Susan Jones
PER00017	D	Mabel Smith
ACC00003	P	Arturo Gomez

<tbody>
</tbody>

Then what I want to see is an automatically generated (and updated) report like:

Name	Job title	Reason		ACC	FIN	PER
Arturo Gomez	Account clerk	Accounts project		Y
Joe Brown	Admin clerk	Accounts project		Y
Mabel Smith	Project Manager	Project Mgt			Y	Y
Susan Jones	HR Manager	Interviews				Y

<tbody>
</tbody>

Columns 2 and 3 ('job title' and 'Reason') are not in the main source table. I'd like to either fetch these from a 'personnel' table, or have the opportunity to manually enter them. If records are added or deleted from the main table, I'd like this report table to update somehow, automatically extending or shrinking the number of rows as necessary. Basically, I want to automate the far right-hand side of the report table (those last three columns with the department summaries in), as otherwise manually maintaining this information is a considerable chore..

Many thanks in advance everyone..

PP

 

[Mohammad Basem]

Have a look at this file. A pivot table has been used to show the summary.
One additional column was added to extract the first three characters of the ID.

 

[pilgrimpez]

Hi Mohammad

Ah, I think I see how that's all put together and working.. That looks exactly the kind of thing I was after - I'll play a bit further with that so I understand it completely.

Many, many thanks for that!

PP

 

[Mohammad Basem]

Al-hamdullah, you are most welcome and thank you for the feedback!