• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting defect..

akinkaraman

Member
Conditional Formating doesn't show the cells in a color at the following file. I couldn't find a solution. Can you help me to fix it?

Note : Please do not unmerge the cells. They can not be in the orriginal file.

Thanks.
 

Attachments

  • Conditional Format.xlsm
    17 KB · Views: 8
Can you tell us what the CF is supposed to be doing?

Currently, it ignores the value in the cell, and is appearing to show where each zone's value in row 4 lines up with zone name in row 8 falls into a number bucket in col C.

Your formula has two parts, comparing to lower and upper boundary. The upper boundary part is good, in that it looks for the dash. The lower portion causes a problem as it looks for a space, but cells C14, C23, and C25 do NOT have a space before the dash. This is causing the formulas to error out, and not give a TRUE result where needed. So, to fix it using current setup, need to correct those 3 cells.

OR....
we can improve the worksheet by changing the CF rules, and go down to a single CF rule.
Ammeded CF formula:
=AND(INDEX($C$4:$Z$4,MATCH(G$8,$C$5:$Z$5,0))>=VALUE(LEFT($C9,FIND("-",$C9)-1)),INDEX($C$4:$Z$4,MATCH(G$8,$C$5:$Z$5,0))<=VALUE(RIGHT($C9,LEN($C9)-FIND("-",$C9))))
Apply to range G9:R27
 
Last edited:
Yes Luke, the problem is because of the spaces. I couldn't see that. And with your one conditional formatting formula works instead of all these different formating formulas.

I will always be appreciated to you guys. Wonderful.
 
Hello Mr. Luke. Can you check my following file?

I am trying to make my first page has the same conditional formating which has at like second page.

The formula is very short : =((C$7>(--LEFT($B11,FIND(" ",$B11,1)-1)-1))*(C$7<(--RIGHT($B11,LEN($B11)-FIND("-",$B11,1)-1)+1)))=1

But I couldn't make 1 conditional formating formula that is why I wrote many for every column. Can I make same 1 formula for my first page. You did 1 formula but this is a short formula to have it.
 

Attachments

  • Conditional Format.xlsm
    21 KB · Views: 9
Last edited:
Hello Narayan, Can't we do it without intersect the cells like second page?
I will not be able to split them in original file.
 
Last edited:
Hi ,

By intersect , do you mean the helper columns ?

Certainly it is possible ; I just wanted you to see the logic that is being used ; it is the same in both tabs ; all that is required is to change the addresses to suit.

Narayan
 
I couldn't understand. In your formula you use AC9 and AD9 instead of C9.

You've splited C9 to 2 cells. How will it be possible to write it as C9?

your conditional format formula:
Code:
=AND(HLOOKUP(INDEX($G$8:$R$8,COLUMN(G9)-COLUMN($G$9)+1),$C$1:$Y$4,4,YANLIŞ)>=$AC9,HLOOKUP(INDEX($G$8:$R$8,COLUMN(G9)-COLUMN($G$9)+1),$C$1:$Y$4,4,YANLIŞ)<=$AD9)
 
Hi ,

I have split up C9 into 2 cells only so that you can understand the logic.

The CF formula is as follows :

=AND(HLOOKUP(INDEX($C$10:$N$10,COLUMN(C11)-COLUMN($C$11)+1),$C$4:$N$7,4,FALSE)>=$P11,HLOOKUP(INDEX($C$10:$N$10,COLUMN(C11)-COLUMN($C$11)+1),$C$4:$N$7,4,FALSE)<=$Q11)

where the formula for P11 is :

=LEFT($B11,FIND("-",$B11) - 1)+0

while the formula for Q11 is :

=MID($B11,FIND("-",$B11) + 1,999)+0

You can certainly put these into the main formula so that the helper columns are eliminated.

See the attached file.

Narayan
 

Attachments

  • Conditional Format.xlsm
    22.8 KB · Views: 1
we can improve the worksheet by changing the CF rules, and go down to a single CF rule.
Ammeded CF formula:
=AND(INDEX($C$4:$Z$4,MATCH(G$8,$C$5:$Z$5,0))>=VALUE(LEFT($C9,FIND("-",$C9)-1)),INDEX($C$4:$Z$4,MATCH(G$8,$C$5:$Z$5,0))<=VALUE(RIGHT($C9,LEN($C9)-FIND("-",$C9))))
Apply to range G9:R27

If we continue with this formula if there are more then 1 same Zone, it colors the first one, it doesn't show second one too.

Such as in new file there are more than 1 A zone. It sees and shows the one I4 but it doesn't shows the second one which is at Q4

How can we fix this?

I realized that Mr Luke's formula is better than the original formula that I was trying to use. The other formula can not detect if the zone changes.

Awesome..
 

Attachments

  • Conditional Format.xlsm
    16.6 KB · Views: 2
Last edited:
Hi ,

If you can say what exactly you want done , developing the formula will be easier ; I don't think you have done that so far , since we only seem to be discussing formulae !

Just explain in one sentence what you want done , that is all ; the formula will come in 10 minutes.

Narayan
 
There are some weights in the upper side. Below the weights it shows their Zones. What I want to show the index numbers of those weights in the below table according to their zones.

Such as in my last sample file there are 1 A zones : 3000 and 4001 and there are 2 M zones : 2001 and 201

I want to show different color 2500-3000 for 3000 and 4001-4500 for 4001 at A zone and 2001-2500 for 2001 and 201-500 for 201 at M zone.

Luke's formula shows the one which comes first and ignores second zone which is same zone.
 
Last edited:
Hi ,

I am sorry but I cannot understand anything of what you have explained ; it will be easier if you can say which cells you want colored in any one column , say column J.

If we consider the cells J9 through J27 , which cells should be colored , and why ?

If column J does not have more than 1 colored cell , then please take the example of a column which has multiple cells colored and explain with reference to that column.

Narayan
 
Hi Akin,

See this file, I had used lot of helper cells to do the CF for zones A-F. Just see if this correct.

Regards,
 

Attachments

  • Conditional Format (1).xlsm
    26 KB · Views: 5
Mr. Somendra, it is working great. I have no speech that I can say more. Wow!

Thank you very much. :)
 

Attachments

  • Conditional Format.xlsm
    20.1 KB · Views: 0
Last edited:
Hi ,

It is good that you have got a solution to your problem , but I think you should introspect why it took so long to arrive at the solution.

1. The first file you uploaded had zones A through M , each of them occurring once.

A formula was proposed which catered to this sample file.

2. The second file you uploaded had zones A through M , where zones A , C and M occurred twice while other zones occurred only once.

A formula was proposed which catered to this sample file.

3. The third file you uploaded has zones A through L , with zones A and L occurring thrice.

Misra has given you the solution.

Where do you think the problem lies ? In your English ? Or in the uploaded file ?

Narayan
 
I am sorry Narayan, I didn't mean to make confused but I guess gave a lot.

Weights's Zones change, that is why all weights has Zone fields. I will try to be more clear next time. It is my fault. :oops:
 
Hi ,

No issues , but the point is if the sample file which is uploaded is a real specimen file , with realistic data , and which captures all the variations that will be present in your working file , then the correct solution may emerge on the first attempt itself.

Otherwise , a lot of time and effort may be spent in getting to the solution.

Narayan
 
@akinkaraman

I totally agree with Narayan Sir, the key to get a precise solution is to provide a an accurate explanation or a sample file with all type of variations in it. Now, the problem comes with most of the users coming to forum is that they had already designed something and they get stuck in between in some process for which they look help for. So solution is proposed based on that, it's users responsibility to look in it and introspect what will be the outcome on this result on the future development.

So a manual working on how they want it to look like with all types of possibilities in a sample file saves a lot of your time as well as of those who are proposing solutions.

Regards,
 
Back
Top