Forum Discussion
Conditional Formatting controlled by Drop Down
thanks for the screenshots. Ok, this is a bit of a challenge š
I prepared a proposal in the attached file. It requires that you maintain all the blackout date ranges in a single table, like this:
This table gets loaded into Power Query and gets transformed to a list with all single blackout dates and then loaded back into Excel, like this:
If you change or add apps or date ranges in the blue table, all you need to do is to refresh the green table with a right-click.
That green table is then the base for the conditional formatting rules. As you have now every single relevant day per app, you can easily setup the appropriate rules, e.g.
=COUNTIFS($R$5:$R$232,$B$2,$S$5:$S$232,A5)<>0I hope, this helps.
Kind regards,
Martin
Hello Martin_Weiss, thanks for taking the time to look at this. I did not realize that the system does not allow for .xlsx file to be uploaded so here are screenshots. The fist image is the main sheet, second the legend and lists, third, fourth, and fifth the blackout dates, and the final the end state after selecting each app in cell B2. Hope that shows my dilemma.
āāāā
thanks for the screenshots. Ok, this is a bit of a challenge š
I prepared a proposal in the attached file. It requires that you maintain all the blackout date ranges in a single table, like this:
This table gets loaded into Power Query and gets transformed to a list with all single blackout dates and then loaded back into Excel, like this:
If you change or add apps or date ranges in the blue table, all you need to do is to refresh the green table with a right-click.
That green table is then the base for the conditional formatting rules. As you have now every single relevant day per app, you can easily setup the appropriate rules, e.g.
=COUNTIFS($R$5:$R$232,$B$2,$S$5:$S$232,A5)<>0
I hope, this helps.
Kind regards,
Martin
- Thank you kindly Martin_Weiss, I will try this method. I have never used Power Query before so this will be a great learning experience. I appreciate you taking the time to help me.
Although you're not able to post the spreadsheet here in this forum, you do have the option of posting a copy of it on OneDrive or GoogleDrive and pasting a link here that grants access to it. That way we could do more than attempt to interpret the images....
- Thank you mathetes for the tip.
And thank you Martin_Weiss, I followed your recommendation and I got this working. I appreciate this very much.
