'Format all cells based on their values', using values outside of the cell

Hey guys, I'm fairly new to this whole thing, but needed help with a spreadsheet I'm working on.

The spreadsheet is being used to keep track of stock we are producing for upcoming shows and events. So I've been using the 3 colour scale option to show how close we are to our target stock. But herein lies the problem. Originally I was just using our original target stock numbers for the current quantity totals (for example, on row 14 we want 70 of the product, so for D14 I've put in "0", "35", "70" for "Minimum", "Midpoint", and "Maximum" respectively), but since then, some of these numbers have changed. What was originally 180 of a product has become 350.

So my question is (and I'm sorry if I've worded this clunkily at all), is there any way to make the "Maximum" field in the 3 colour scales be a value from outside of that cell, so that I don't have to go around reformatting every individual cell every time it's decided the quantity is too low? Can I make it read the data from E14 rather than have to manually change the numbers?

Thanks!

Hi, welcome to the forum,

I'm doing this from memory, as I'm on 2003 at work which doesn't allow 3-colour scales, but if I remember rightly, when you select 3-colour scale, there are three rows of boxes labelled 'Type' 'Value' and 'Colour'. I take it you've entered 0/35/70 manually into the 'Value' boxes? Instead, do the following:

Minimum: Type 'Lowest Value'
Value '0' (manually entered)

Midpoint: Type 'Percentile'
Value '50'

Maximum: Type 'Highest Value'
Value '=$E$14'

$E$14 is selected by clicking the symbol at the right of the box with the little red arrow, clicking on E14, then clicking the symbol again. The $ symbols mean that the reference won't change if you copy/drag the formatting to another cell. If you want it to always refer to column E, but the row to change as you copy/drag it up/down, then change it to $E14.

You might find these pages of help:
https://support.office.com/en-us/art...rs=en-US&ad=US
https://support.office.com/en-au/art...rs=en-AU&ad=AU
https://support.office.com/en-us/art...n-US&ad=US#bm3

I hope that helps.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Originally Posted by Aardigspook

Hi, welcome to the forum,

I'm doing this from memory, as I'm on 2003 at work which doesn't allow 3-colour scales, but if I remember rightly, when you select 3-colour scale, there are three rows of boxes labelled 'Type' 'Value' and 'Colour'. I take it you've entered 0/35/70 manually into the 'Value' boxes? Instead, do the following:

Minimum: Type 'Lowest Value'
Value '0' (manually entered)

Midpoint: Type 'Percentile'
Value '50'

Maximum: Type 'Highest Value'
Value '=$E$14'

$E$14 is selected by clicking the symbol at the right of the box with the little red arrow, clicking on E14, then clicking the symbol again. The $ symbols mean that the reference won't change if you copy/drag the formatting to another cell. If you want it to always refer to column E, but the row to change as you copy/drag it up/down, then change it to $E14.

Hey, thanks for that. But unfortunately it doesn't work. When lowest value and highest value are selected, the boxes below have greyed out text saying '(Lowest value)'/ '(Highest value)', so manual entry is impossible. And I've tried entering '=$E$14' under 'Number' and 'Formula' previously, but that only seems to result in cell D14 staying constantly on the mid point yellow colour if any number from 1-69 is entered in. Any higher or lower and it jumps to the minimum/ maximum colours.

Ok, now that I'm at home I can check 2010...

Set both Minimum and Maximum to 'Formula'. In 'Value' for Minimum, type =0 and in 'Value' for Maximum, type =$E$14 (or $E14 or =E$14 depending on if/how you want the formula to be copied/dragged).
The attached file shows this in action.
3_colour_scale based on other cell.xlsx

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Had another think about this. Another possibility is to use data bars, which would show the cell 'filling up' as the stock approached maximum. Again, use formulae =0 and =$E$14 to set the minimum and maximum. See attached.
3_colour_scale or data bars based on other cell.xlsx

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.