Log In
Register
Home
Active Member
September 7, 2020
Offline
Hi there,
I am working on this Project Timeline template in Excel. I wonder if I can integrate the filter button on the activity column to the calendar above. For example, I filtered the activity schedule to only Milestone 1 and I only want to see milestone 1 in the calendar above as well.
July 16, 2010
Offline
Hi Aileen,
Welcome to our forum! As I mentioned in reply to your question via email, you need to attach your Excel file so we can see what you're referring to. Please explain what you want in reference to the sheets, cells and inputs of your file. Keep in mind that you know what you want and are familiar with your file and data, but we'll be seeing it for the first time.
Thanks,
Mynda
Active Member
September 7, 2020
Offline
Thanks for your reply Mynda, sorry I thought I have already attached my file.
I hope my file is being attached now.
What I want is that, when I filter the activity function on cell C4, I also want my Calendar on cell C2:G2 shows only the activity schedule that I had filtered through cell C4.
I would be working with planning survey dates and for each survey, I have different dates (post out the schedule, return date, and lots of reminder dates). Do you think it is a good idea for me to use this template?
July 16, 2010
Offline
Hi Aileen,
Thanks for sharing your file. You need to add a column to your Activity Table (which you can hide) that detects if the row is visible/not filtered. You can use this formula:
=(AGGREGATE(3,5,[@ACTIVITY])>0)
Or if you don't have AGGREGATE you can use SUBTOTAL:
=SUBTOTAL(3,[@ACTIVITY])
You then multiply the formulas feeding your chart by the values in the Visible column. If it's true i.e. visible, it will return the value, and if it's false it will return 0.
Hope that helps.
Mynda
Active Member
September 7, 2020
Offline
Hi Mynda,
I have added your suggestions, however, the problem is with the calendar, when I filtered my table only for Milestone 1 and 2, the calendar above still shows Project Start and Milestone 3.
Attached below is my updated file
July 16, 2010
Offline
Hi Aileen,
You haven't done this part: "You then multiply the formulas feeding your chart by the values in the Visible column. If it's true i.e. visible, it will return the value, and if it's false it will return 0."
TBH, this is an awfully convoluted way to build this chart and I don't have time to reverse engineer all the formulas. However, I trust you understand how this file is built and can work back to the point where you can use the information in the Visible column to control what rows are returned in the table that feeds the chart. There are some very advanced techniques in this file so I expect you should be able to follow the concept, which is to use the TRUE/FALSE values in the Visible column to hide (or return errors for) the rows that aren't required in the chart.
If you inherited this file and you're not sure how it works, then I would highly recommend rebuilding it in a simpler way. It really doesn't need to be this complicated. For example, download the Excel file for this Project Management dashboard and you'll see a far simpler way to achieve a very similar result.
Mynda
1 Guest(s)
