Pivot table with monthly reports - how to show only current months report row

Hi,

Hoping I can explain this clearly..!

I have created a dashboard to show my team's tasks over the year. For non-repeating tasks it has been easy. However there are a lot of reports that are repeated monthly.

In my source data I had each report on 1 row, and just did a formula on the start date cell so it would update every month to reflect the most current date that the report should be run on.

This looks fine on the pivoted results, the issue is I also have a gantt style time line with conditional formatting for each task. And of course the report is only showing up on 1 month. I need the tasks to show on this gantt chart every month. But I can't work out how to do it? We don't want each monthly task to show up as text as this will look awful as there will be 100s.

In summary, i need each instance of the monthly report to be reflected in the gantt chart, without having the actual text of them show up in the pivoted table.

Hope this is clear. I have attached a mock up (this currently doesn't have the conditional formatting on).

Thanks in advance.

Do you mean that the 123 Report should highlight both 6/10 through 6/12 and also 7/10 (last date shown)?
Should the weekly tasks also highlight repeatedly?
It may help us understand if you could manually highlight cells on the dashboard sheet that you would like to see highlighted using conditional formatting.

Hi JeteMc. Thanks for your reply.

So I would want the tasks to highlight repeatedly in the date chart yes, but only show once as text. So 123 Report I would want coloured in on the date chart once a month, and so on. I am not sure if this is at all possible!

Just for info, on the proper file I have created, I have a sequence for the dates for the whole of 2022, and a scroll bar across the top to scroll across them.

I have amended the file to show what I mean, thank you for taking the time to look.

There is something like what I need here but can't get it to work for me https://www.mrexcel.com/board/thread.../#post-5761731

This works as a conditional formatting rule for the sample provided:

Formula:

Please Login or Register  to view this content.

Note that the formula produces the green highlighting and that in row six there are differences in the cells highlighted by the formula and those that were manually highlighted starting with 7/7/2022.
Note that rows 11:16 are not needed, however I left them in the event you want to test how the formula works in different rows.
Let us know if you have any questions.

Hi JeteMc, thank you SO much, this worked perfectly! Sorry for the late reply, i've only just had time today to get this into my spreadsheet. I managed to make it work with my other conditional formatting too so am very happy. Thanks again!

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.