Conditional Formatting

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

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

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.

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......

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...

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?

Originally Posted by 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?

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

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:

Please Login or Register  to view this content.

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.

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:

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

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

That is perfect!

Thanks NBVC!