Colored cells

[MrMix]

Hello,
I have a file with two sheets, sheet2 has several rows with cells containing the formula =+'sheet1'!B2, =+'sheet1'!C2, =+'sheet1'!D2 and so on
Now I'd like to highlight from a specific range the cell that has the lower value. I wrote the formula =B2=Min($B2:$G17) in Conditional Formatting -> Use a formula to determinate which cell to format and
then selected to cell color.
Unfortunately it does not work !
I believe I know why it does not work but I'm not able to find a solution.
Now if I copy all the range and I past the values in a new sheet then I apply the above Conditional Formatting procedure I get the lower cell value colored.
Could you please help to get it work ?

Thank you in advance
Regards
MM

 

[Joe4]

Welcome to the Board!

I believe I know why it does not work but I'm not able to find a solution.

What is the reason, you think?

Are the values it is pulling over numeric, or numbers entered as text?
If you go to Sheet1, and in any blank cell, enter this formula:

=ISNUMBER(B2)

does it return TRUE or FALSE?
If it returns FALSE, you are dealing with Text entries, which is why your MIN formula is your working correctly.

It might be helpful to show us a sample of your data. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MrMix]

What is the reason, you think?

Even you see values in sheet2 cells there are just formulas (=+'sheet1'!B2) while in the working sheet cells there are 'really' values.

 

[Joe4]

Even you see values in sheet2 cells there are just formulas (=+'sheet1'!B2) while in the working sheet cells there are 'really' values.

Nope, that should not be the reason. Excel does not care if the values are hard-coded or the result of formulas, as long as they are returning "real" values, and not "numbers entered as text".

Did you try entering the formula I mentioned in my previous post to see what it returns? That could hold the key to the issue.

 

[MrMix]

Did you try entering the formula I mentioned in my previous post to see what it returns? That could hold the key to the issue.

Yes, in both sheets (working and not working) the result is TRUE

 

[Joe4]

OK, then you might have set-up the Conditional Formatting wrong.

What range did you select exactly before entering your CF formula?

 

[MrMix]

OK, then you might have set-up the Conditional Formatting wrong.

What range did you select exactly before entering your CF formula?

I made again the selection and typed again the formula.
Now it works !!
Thanks
MM

 

[Joe4]

You are welcome!

Many times, the issue is that the range selection and the formula are not coordinated with each other.
The formula entered has to reflect the first cell in your range selection. If there are misaligned, it won't work properly.

A common mistake is people will select entire columns, but they want to skip the first row (header row), so they write the formula reflecting row 2.
But if you select an entire column, the first row in your selection is row 1! That would mean that row 1 is looking at values in row 2, etc.
So they either need to change their selected range to start on row 2, or change their formula to look in row 1, so everything is in coordination.