Conditional formatting on returned value

[finalfrontier1976]

Hi,

I have an issue trying to get a conditional formatting set up on returned values from formulas. I have a worksheet with formulas in it that return values from another worksheet in the same workbook. I want to keep the formulas in the cells but impose a CF based on the value of the percentage that the formulas are bringing back.

For example, if he returned value is between 0% and 5% then fill the cell green.

So far no success.

Any ideas out there?

 

[Joe4]

Welcome to the Board!

Conditional Formatting does not care whether or not the values in the cells are hard-coded, or returned by formula. It makes no difference.
So, if it is not working, there is probably an issue either with the data it is returning, or your Conditional Formatting rules.

Is the data that the formula returning actually numeric, or numbers formatted as text?
Usually a dead giveaway is how the data is appearing in the cells. If it is left-justified, it is probably text. If it is right-justified, it is probably numeric.
The format of this data will impact how you need to create your Conditional Formatting rules.

To that point, please also post the Conditional Formatting rule that you have set-up.

 

[finalfrontier1976]

Welcome to the Board!

Conditional Formatting does not care whether or not the values in the cells are hard-coded, or returned by formula. It makes no difference.
So, if it is not working, there is probably an issue either with the data it is returning, or your Conditional Formatting rules.

Is the data that the formula returning actually numeric, or numbers formatted as text?
Usually a dead giveaway is how the data is appearing in the cells. If it is left-justified, it is probably text. If it is right-justified, it is probably numeric.
The format of this data will impact how you need to create your Conditional Formatting rules.

To that point, please also post the Conditional Formatting rule that you have set-up.

Hi,

I took out all he CF rules and tried to apply it again using this:

Cell value between 0 and 5

The returned data are percentages, the cells themselves are set to general and is left justified.

 

[Joe4]

Cell value between 0 and 5

This is your problem right here.

Percentages are fractions of 1 (where 100% actually equals 1).

So you either need to do:
Cell value between 0 and .05
or
Cell value between 0 and 5%

 

[finalfrontier1976]

This is your problem right here.

Percentages are fractions of 1 (where 100% actually equals 1).

So you either need to do:
Cell value between 0 and .05
or
Cell value between 0 and 5%

Strange, that didn't work either.

 

[Joe4]

The returned data are percentages, the cells themselves are set to general and is left justified.

Whoops, I overlooked that part in your last reply.
That seems to suggest that you are pulling back text, not numbers.
What exactly is the formula in the cell that you are applying the Conditional Formatting to?

And the cell that the formula is pulling from, is that hard-coded or a formula (if a formula, please post that formula too)?
Is the data left or right justified in that cell?

 

[finalfrontier1976]

Whoops, I overlooked that part in your last reply.
That seems to suggest that you are pulling back text, not numbers.
What exactly is the formula in the cell that you are applying the Conditional Formatting to?

And the cell that the formula is pulling from, is that hard-coded or a formula (if a formula, please post that formula too)?
Is the data left or right justified in that cell?

Ok, the returned formula is (the one I'm trying to CF): =IFERROR(VLOOKUP($A12,INDIRECT("'"&B$11&"'!A:F"),6,FALSE),"")
It's left justified on return automatically not set in the ribbon.

The source figure did say it was formatted as text but I converted it to a number and it's purely a percentage like 4.76% no formula and it's centred.

 

[Joe4]

it's centred

If it is centered, it probably has custom justification set on that cell. If you remove it so it goes back to the default, I am guessing it will be left-justified.
Just to confirm that, you can use the ISNUMBER function to check:
=ISNUMBER(F6)
If it returns FALSE, it is not a number, so numeric functions will not work on it (like >, <, between, etc).

So we have a few choices:
- Convert the source data to numeric (can be done easily with Text to Columns)
- Change your Conditional Formatting rules to convert the values it is check to numbers (in which case, you would have to use the "Formula" options)
- Update your formula to convert the value it is bringing back to a number, like this:

[COLOR=#333333]=IFERROR([/COLOR][COLOR=#ff0000][B]VALUE([/B][/COLOR][COLOR=#333333]VLOOKUP($A12,INDIRECT("'"&B$11&"'!A:F"),6,FALSE)[/COLOR][B][COLOR=#ff0000])[/COLOR][/B][COLOR=#333333],"")[/COLOR]

Note that with this last step, the appearance of the percentages may change to decimals. But that can easily be changed with a custom format on the cell/column.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...-formatting-on-cells-containing-formulas.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.