• 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

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

  • Test.xlsx
    14.4 KB · Views: 4
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

  • Chandoo47029Test.xlsx
    38.5 KB · Views: 5
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).
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:
Back
Top