I have a pivot table with date as row and a bunch of funnel stages as the columns.
There are three filters that will affect the table.
I want to create a conditional formatting using the ‘visual cues’ tab or any other alternate ways by comparing these values to a set of bench marks ie I want to give different background color for each data point in the pivot table using these benchmarks. There is an upper limit as well as a lower limit.
The problem is, whenever a filter or a combination of filters is selected, the benchmarks change accordingly.
So if the benchmarks are stored in a separate table, how can I use it to create the colors? Is it possible??
For every date, in each column, for every set of filters, a background color need to be applied. Is there a function like vlook up which searches in the benchmark table for the right date and set of filters, retrieve value which can be used in the pivot table to set limits for background color ????