Conditional Formatting

[NickPDC]

Hi there,

I am trying to do some "Complex Conditional Formatting", the complex meaning that I cannot work out how to do it..

I have attached the sheet I am working from to this post for anyone to attempt this if they are feeling brave enough.

To be quite honest, I am unsure if it can actually be done, but here goes nothing.

Sheet "1" on the workbook, is basically day 1 of the month, hence the 30 other "days".

On Sheet 1, there is a drop down box at the top, in the "Part No" field.
This basically contains the parts numbers of everything manufactured. Once a part number has been selected, this populates some of the other fields with data, the one I am interested in is the "Grade" field.

If you look to the right hand side of the sheet, there are such fields as, Coin No, Time, ACEL, C, Si etc.
Data is entered into these cells. But the data being entered has to be within a certain range. The range that this data can be is related to the "Grade" at the top of the sheet.

If you look on the "GradeSpec" sheet at the end of the workbook, this contains all of the different materials for each grade, and their upper and lower limits.

Basically, in a nutshell.

The Grade for each part number differs, with each grade comes a different set of limits for the data being entered into "ACEL, C, Si" etc.
I would like some conditional formatting that is able to look up what the limits are for a grade, and if a number is entered into the field that does not comply with these limits, for it to turn the cell red.

If anybody has any questions please let me know, as I am stumped as to where to start!

I would be very appreciative if someone would be able to help out with this problem

Thanks,

Nick

[daddylonglegs]

Hello Nick,

You should be able to use VLOOKUP to do this, e.g. you can use a formula like this in Conditional formatting

=U29+0>VLOOKUP($C$6,Table_pdcerp01_LIVE_PDC_App_EUR_BackupViewerPDCGrade,3,0)

That compares U29 (Coin number) against the 3rd column of the table (Lower limit) where the Part number matches the one shown in C6

[NickPDC]

Hello and thank you for the quick response!

U29 is the wrong column, sorry for the confusion!
It is the line of Additions above that block, found at X16 so on and so forth.

I entered the formula into "Conditional Formatting - New Rule - Use a formula to determine which cells to format" and got an error message.

I am using Excel 2010. I must update my Forum Profile!

Basically needs to tell the user of the spreadsheet that the data they entered is not within the spec of the grade at C6.

[daddylonglegs]

Hello Nick,

What I'm suggesting is that you use the VLOOKUP formula within conditional formatting. In my example the VLOOKUP is used to retrieve the Lower limit for the part number at C6......so this example might not make sense but it demonstrates the principle....

If you have a value in X16 and you want that highlighted red if it is lower than the lower limit for the part number in C6 as specified in your table then in conditional formatting (for X16) you can apply this formula:

=X16+0<VLOOKUP($C$6,Table_pdcerp01_LIVE_PDC_App_EUR_BackupViewerPDCGrade,3,0)

If you have JD32A in C6, for example then X16 will be red if it's less than 4.07, change the part number in C6 and the limit will change accordingly because VLOOKUP in conditional formatting will find the new value......

[daddylonglegs]

OK, hold the backpage.....

I see what you mean now, I get an error with that formula too......it works on the worksheet so the formula is valid.....it may not work in CF because it references a table name rather than a named range....let me investigate...

[daddylonglegs]

OK, I tried giving the table a defined name (rather than using the actual table name) - I selected whole table and used Name Manager on formulas tab to rename the table New_Table_Name and then I used this formula in CF

=AND(X16<>"",X16+0<VLOOKUP($C$6,New_Table_Name,3,0))

see example attached

Now thinking that your table includes "Ingredient" too that can alter specifications....does that make any difference?

[NickPDC]

OK, I tried giving the table a defined name (rather than using the actual table name) - I selected whole table and used Name Manager on formulas tab to rename the table New_Table_Name and then I used this formula in CF

=AND(X16<>"",X16+0<VLOOKUP($C$6,New_Table_Name,3,0))

see example attached

Now thinking that your table includes "Ingredient" too that can alter specifications....does that make any difference?

That is it!
Hit the nail on the head with that, works great!

I am guessing there wouldn't be any way of incorporating the higher limit in as well?

The columns within that block that need the Conditional Formatting applied to them are:

ACEL
Mo - Moly
Cu - Copper
C - Carbon
P - Phosphorous

Thanks for all of your help so far, I am very grateful!

With regards to the "ingredient" column, what table is that in? The "GradeSpecs" worksheet?
If so, the ingredient is what refers to the column on the "1" worksheet that data is being inputted into, so there are different limits for each ingredient.

The little list above demonstrates what letters correspond to what ingredient.

After a bit of testing, the limits change when the grade changes, which is 100% correct, but the limits do not seem to be correct for the "Carbon" element in the grade "JU12A".

Thanks,

Nick

[NickPDC]

Anybody able to shed anymore light on this problem?

[NickPDC]

Still needing help with this if anybody is able to

[NickPDC]

I have given this another go, and still cannot seem to get it working.
Anybody have any ideas?

[arthurbr]

Perhaps

=AND(X16<>"",OR(X16+0<VLOOKUP($C$6,New_Table_Name,3,0),X16+0>VLOOKUP($C$6,New_Table_Name,4,0)))

(Haven't checked it )

[NickPDC]

Thanks for taking the time to give it a go but it doesn't seem to be working .

Is this even possible?

I am looking for the ranges to change on each material listed when the "Grade" in C6 changes.

All limits corresponding to this grade are on the "GradeSpecs" sheet.

Both upper and lower limits need to be in place, so that when a number that is entered into one of the different materials columns doesnt comply to the spec on the "GradeSpecs" sheet, the cell or text turns a different colour.

Thanks again

[NickPDC]

Still needing help!

Would be grateful if anyone could give this a go!

[NBVC]

Try this.

Insert a new column in the GradeSpecs sheet between Ingredient and LowerLimit. In this column put in the chemical symbols that match the ingredient names as shown in W14 to AN14 of each daily sheet.

Then change the formula for conditional format of W16:AN25 to:

=AND(W16<>"",OR(W16+0<SUMIFS(INDEX(New_Table_Name,0,4),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14),W16+0>SUMIFS(INDEX(New_Table_Name,0,5),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14)))

[NickPDC]

Try this.

Insert a new column in the GradeSpecs sheet between Ingredient and LowerLimit. In this column put in the chemical symbols that match the ingredient names as shown in W14 to AN14 of each daily sheet.

Then change the formula for conditional format of W16:AN25 to:

=AND(W16<>"",OR(W16+0<SUMIFS(INDEX(New_Table_Name,0,4),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14),W16+0>SUMIFS(INDEX(New_Table_Name,0,5),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14)))

Absolutely brilliant!

Works like an absolute charm, thank you very much .

There is one more thing I am needing to do on this worksheet now that is working!

Basically, the columns on the left hand side, the "ACEL" and "Pour Temp" need to have the same conditional formatting applied to them, as the cells on the right hand side.

I tried copying and pasting the Conditional Formatting formula into the "ACEL" column and it didn't work..

The "ACEL" needs to be exactly the same as the field on the right hand side of the spreadsheet. But it needs to be in the left hand column.

The "Pour Temp" needs to use the same rule as the conditional formatting on the right hand side of the list, but this is done by "Part No", so when the "Part No" changes, the limits of the Max and Min pouring temps change, and these are found in the "Data" worksheet.

Hope you can help

Kind regards

[NBVC]

If you copy cell W16 to B16, then the format should apply in a relative manner. Then you can copy B16 down the ACEL column.

I don't understand the Pour Temp part.. there is no equivalent on the right that I supplied conditional formatting for.

[NickPDC]

If you copy cell W16 to B16, then the format should apply in a relative manner. Then you can copy B16 down the ACEL column.

I don't understand the Pour Temp part.. there is no equivalent on the right that I supplied conditional formatting for.

Ah, that's easier than I first thought!

I thought I would have to copy the rule, not just the cell! Brilliant stuff, thanks for that .

As for the pouring temp, I need it to work in the same way as the formatting on the right hand side. IE, when it is out of spec, the cell turns orange.

The only difference with the pouring temp is, it is done on "PartNo" and not on the "Grade". So when the PartNo is changed, the limits for the Pouring temp also change. The same principle as the right hand side of the worksheet. The data for this cell is found in the "Data" worksheet.

As per the ingredients, this too has upper and lower limits, that when breached, need to be able to turn the cell orange to highlight the fact it is out of spec.

I hope that makes more sense than last time!

Kind regards

[NBVC]

Assuming your are applying to column C starting at C16, then change the current conditional format in that column to:

"use a formula to determine which cells to format"

and apply formula:

=AND(C16<>"",OR(C16<SUMIF(INDEX(Data,0,1),$C$4,INDEX(Data,0,6)),C16>SUMIF(INDEX(Data,0,1),$C$4,INDEX(Data,0,5))))

[NickPDC]

Assuming your are applying to column C starting at C16, then change the current conditional format in that column to:

"use a formula to determine which cells to format"

and apply formula:

=AND(C16<>"",OR(C16<SUMIF(INDEX(Data,0,1),$C$4,INDEX(Data,0,6)),C16>SUMIF(INDEX(Data,0,1),$C$4,INDEX(Data,0,5))))

Yep, that's the correct column. Sorry for leaving that bit out!

With the above formula, the text goes red and italic, but it stays red even when the number is within the "Max & Min".

That, or I am doing something wrong. (Probably me).

EDIT: I did it again and it worked perfectly! I knew I was the problem!

Thanks again for your brilliant help

[NBVC]

Great. You are welcome.

Don't forget to please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[NickPDC]

Hello again!

Unfortunately, due to the formulas used in the worksheet being way beyond my comprehension, I am once again needing help with it!
But this should be a simple problem to solve!

The Conditional Formatting that is applied to the table on the right hand side of the numbered worksheets (1-30) works absolutely perfect, except for 2 columns.

The columns for Cr and Cu need to look at different columns to look up the values they need to change their formatting at!

Currently the rest of the items within that table look at columns 4 and 5 on the GradeSpecs worksheet. These are the LowerLimit and Upperlimit.

The formatting for columns Cr and Cu simply need changing to look at columns 6 and 7 on the GradeSpecs sheet, which are the LowerSecLimit and the UpperSecLimit for each of the numbered sheets.

I have tried the following formula:

=AND(W16<>"",OR(W16+0<SUMIFS(INDEX(New_Table_Name,0,6),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14),W16+0>SUMIFS(INDEX(New_Table_Name,0,7),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),W$14)))

To no avail!

If any one could help solve this it would be great and hugely appreciated.

Cheers,

Nick

The Workbook I am working

[NBVC]

First you need to select W16 and invoke Conditional Formatting.
Change the applies to range to: =$W$16:$AB$25,$AD$16:$AE$25,$AG$16:$AN$25

Then select AC16 and invoke Conditional Formatting:

Change formula to:

=AND(AC16<>"",OR(AC16+0<SUMIFS(INDEX(New_Table_Name,0,6),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),AC$14),AC16+0>SUMIFS(INDEX(New_Table_Name,0,7),INDEX(New_Table_Name,0,1),$C$6,INDEX(New_Table_Name,0,3),AC$14)))

and change the applies to range to: =$AC$16:$AC$25,$AF$16:$AF$25

[NickPDC]

That is perfect!

Thanks NBVC!