I have been searching for a way to conditionally format 1 of three cells (H4, I4, J4) the goal is to turn the cell that is higher than cell C2 but is closest to the number in C2 green to alert the user of the closest match. I found a thread using vlookup to do something similar but I cannot seem to get it to work as an hlookup formula. Anyone have an idea how to accomplish this?
Here!
Put this in Conditional Formatting -
=AND(H4>$C$1,H4=MIN($H$4:$J$4))
Hope it helps!
Deep
Cheers!
Deep Dave
But unfortunately that might not work if the Value in any of the cell is lesser than C2
This is more tuff than it seems.. :O
Last edited by NeedForExcel; 08-12-2013 at 04:42 PM.
It is definitely a tough one. I have tried every combination I could think of without any luck. In this case, maybe it would be easier to say if the value in H4, I4 or J4 is equal to or greater than the value in C2?? Of course the goal would then be to find the number that is either equal to or greater than but is the closest of all 3 cells would turn green with the conditional format...
This seems to be getting me closer but the cells that are either under cell C2 or are higher are still getting highlighted:
Does anyone know how to write the formula differently so that only one cell either H4, I4 or J4 that is greater than (not less than) or equal to C2 but is the closest of the 3 cells to C2to highlight with conditional formatting?
Please Login or Register to view this content.
Try this:
Conditional Formatting:
=H$4=MIN(IF($H$4:$J$4>=$C$2,$H$4:$J$4))
@Teethless mama
Even that does not work -
Assuming the numbers are 54,21,44 & 35 in C2
This is complicated..
Please see the file attached!
This is the best I could come up with. A helper cell used..
Hope it helps!
Deep
Your sample spreadsheet seemed to work well but put a limitation on the maximum number. For instance if 39 is entered into cell C1 then nothing happens. If 38 or less is entered it works properly.
Last edited by tm1274; 08-13-2013 at 08:58 AM.
Yes! That is because there in no entry bigger in Cells H5, I5 & J5 than C1 in the example in the attached sheet. How can it highlight the next biggest number in that case?
Deep
Good point. Only one problem though. It works great in your copy of the spreadsheet but not in mine. Can you think of any reason it would not work in mine after being copied? I copied all 5 cells (includes the helper) and pasted it in, as well as copied each formula and formatting for each cell so it should be exactly the same. For some reason it does not work.
This must have something to do with the cells H4, I4 and J4 being calculations?? What this should do is H4 would equal C13*6 for a value of (1800), cell I4 would then be a calculation of the value of H4*6 for (10,800) and J4 would be the calculation of the value of I4*6 (64,800) and so the conditional format would look at cell C2 and see which of these (H4, I4 or J4) would be the number equal to or greater than but the closest to the number in C2. Would the calculation in each of these cells prevent this from working?
Can you attach your workbook?
Sure, here you go, I think I may have it working right now. But to be honest I am not sure how or why it is working. I copied everything from your sheet and it did not work but after I reopened the sheet it appears to be working...
Calc.xlsx
Last edited by tm1274; 08-13-2013 at 12:59 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks