Hello everyone,



I have attached a spreadsheet, normally I know how to make pivot tables but in this case I'm not sure how to do it.

Y indicates that the block in the left column is applicable to the template

I don't understand how I can see things like overall completion per template, blocks per template, who the people are that are working on which block / template etc.

Do i have to redesign the table to make it work with a pivot?

Thanks in advance

Attached Files

Sample_worksheet_dev.xlsx (12.5 KB, 16 views)

Oprion 1: Use autofilter on your table;

Option 2: Design a report like in attached (updated) workbook.

Attached Files

Sample_worksheet_dev.xlsx (19.3 KB, 17 views)

amazing, thanks! - this is great. Just wondering when I "summarize with pivot table" i don't have the option to filter by template.

Also, just curious how you learned all this? I was looking at the named ranges and formula you used, I could never have thought this up

A time is all you need

Ok cool, and I was wondering when I "summarize with pivot table" i don't have the option to filter by template? how can i add that to my pivot table? so i can make graphs and stuff

What is this 'filter by template' at all? I did some google search, bot got nothing useful!

There is a filtering option for pivot table, but it looks like this is not you are after at?

I myself use pivot tables seldom - mostly I only give help when some user has problem with them. Usually adding a calculated column into original table is enough.

For graphs I prefer report sheets with data validation lists to set up conditions, or I create a ODBC query which returns a table for graph (again, I use data validation lists for selecting query parameters, and the query is set to automatic refresh when any parameter is changed).

How can I learn to do what you did above? is there a tutorial I could find? I'm trying to expand what you showed me and make it a dashboard where I can see specific stats such as per template stats.

So I'm trying to figure out how to calculate for example if template 1 is selected, how much % is it complete. So I can find that information out in 2 ways:

# of blocks completed out of total # of blocks in Template X

or

Add up all the percentage completed, per template, and do an average.

That's why I wanted to create a pivot table, put in "template" and then from there I can look at # of blocks in template, look at who is assigned to working on which blocks within that template etc.

@makavell
Hi
please do not quote entire posts unnecessarily. They clutter the thread and make it hard to read. Thank you

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

ok thanks ArviLaanemets ! i am going to do more research on Advanced Filters

and mark this as solved; i don't understand it though, cannot replicate it