I'm making a spreadsheet journal with :
- a main entry on sheet1
- a static list of names and data on sheet2
In sheet1 I input values through Data Validation based on names in sheet2. Later on I add a value in sheet1. This value needs to be compared to a minimum and maximum value, that is listed in sheet2, and then color coded through conditional formatting based on a % deviation from the values in sheet2. For instance:
In sheet1 I have the data:
A B
1 value OG
2 British Brown Ale 1.045
value British Brown Ale is input through the dropdown populated from the Data Validation list. In B2 I have a value for "OG" being 1.045.
In sheet2 the data looks like:
A B C
1 value min OG max OG
2 British Brown Ale 1.040 1.052
In the row with "British Brown Ale":
- in column 2 ("Min OG") I have a value of
1.040 - in column 3 ("Max OG") I have a value of
1.052
Desired Result
I need to make:
- the range from 1.040 to 1.052 100% within the range and color code that green,
- If the value in
sheet1!B2was for instance up to 10% outside of this range it would be color coded yellow, - and over 10% would be color coded red.
How do I use conditional formatting that can compare values like above?
Will I need scripts to achieve this?
