I have setup conditional formatting using a green-red percentile color scale.
This works fine until I copy this section down the sheet. When I do this, the numbers in the second section affect the color scale applied to the first section.
In this screenshot you can see the colors in the "CPA" row in the second section are affected by the values in the first section. All of them are red, but the smallest in that row should be green and the largest red. This is because the conditional formatting is including the numbers from the first section CPA row in its percentile calculations.
I'm looking for a way to be able to copy and paste the "template" multiple times down the sheet, and have the the conditional formatting rule apply to each pasted copy, however the conditional formatting in each pasted copy should only consider the values in that specific row.
Here's a Google Sheet as a demo: https://docs.google.com/spreadsheets/d/1OMA9ps5AHsB6x7t59UuCvsQ_gw3qnXjUeeDizDXgr68/edit?usp=sharing
The report template looks up values in columns A:D and pulls in figures from the data sheet. So by copying and pasting the template (E2:J8) the figures are pulled in for each client.
In the CPA row, the smallest number is green and the largest is red. For the Leads row, the largest number is green and the smallest is red.
In ColA there is a number loop from 1-8. The template should always be pasted in a row with number 1 in ColA.
In case it is helpful, the CPA row will always have a value of 5 in ColA and the Leads row will always have a value of 6 in ColA. (Not sure if this is helpful for using INDIRECT in conditional formatting?)
I'm looking for a conditional formatting formula that will isolate the percentile scale to just that row, even if we copy it down 50 times. From what I've read I think the INDIRECT function might be helpful in the conditional formatting formula, however I can't figure out how to use it when we're copying and pasting down.
(The Google sheet linked is set to "anyone can edit" so If you want to, feel free to duplicate the "report" sheet and edit the duplicate to test a solution.)