• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Want the pending, ongoing and completed tasks

K

kamal.ab

New Member
Hello All,
I have an excel file with 3 users (wound increase too). all sheets have the daily tasks of the users. I want to collate all in a file automatically to have a view on the completed, pending and ongoing tasks appended one below the other. Any help on the formula?
Sample file attached
 

Attachments

Instead of having separate sheets for Completed, Ongoing and Pending, would putting them on a single sheet with the option of choosing which subset you want to see work for you?
In the attached, on the Completed sheet, there's a table at cell K3 and a selection box (slicer) at cell Q1.

You don't have to use a pivot, a plain consolidated single table will do, see cell I1 of the Ongoing sheet and its slicers to the right of it.
The downside to using a plain table is that all we're doing is filtering it, and whole rows of the sheet are hidden so hiding other stuff outside the table too.
The downside of a pivot is that if you have duplicate rows in the source data (all 7 fields would have to be duplicated) you'll only see one of them (does that matter? perhaps it's a plus).
 

Attachments

p45cal said:
Instead of having separate sheets for Completed, Ongoing and Pending, would putting them on a single sheet with the option of choosing which subset you want to see work for you?
In the attached, on the Completed sheet, there's a table at cell K3 and a selection box (slicer) at cell Q1.

You don't have to use a pivot, a plain consolidated single table will do, see cell I1 of the Ongoing sheet and its slicers to the right of it.
The downside to using a plain table is that all we're doing is filtering it, and whole rows of the sheet are hidden so hiding other stuff outside the table too.
The downside of a pivot is that if you have duplicate rows in the source data (all 7 fields would have to be duplicated) you'll only see one of them (does that matter? perhaps it's a plus).
Click to expand...
Hi,
this would be more on the google sheets and hence seems a bit less feasible
 
I haven't used Google Sheets, but went to have a look and managed to get something working (it's unlikely to be 'best-practice'!).
Create a new sheet and in cell A1 enter the formula:
=ArrayFormula('user 1'!A1:G1)
In cell A2 enter the formula:
=sort({'user 1'!A2:G;'user 2'!A2:G;'user 3'!A2:G})

Perhaps better:
just 1 formula in cell A1:
=query({'user 1'!A1:G;'user 2'!A2:G;'user 3'!A2:G},"select * where Col1 is not null",1)

This should give you a consolidated table, now filter it on the Status column, or create a pivot table from it.



This is an Excel site, not a Google Sheets site. You risk annoying people big time by not telling people you're using Google Sheets (surely there are Google Sheets help forums too).
I had my time wasted completely the other day in this thread: https://chandoo.org/forum/threads/a...-and-row-averages-with-specific-format.47007/
How keen do you think I'll be to help JuliusV again?
 
Last edited:
You must log in or register to reply here.
Back
Top