conditional format has me stumped

[frdranger25401]

I have been working on a conditioning format that looks right but when its entered it returns what im not asking it to do. Here are 3 pictures of the 2 different pages im using. What im trying to accomplish is if Clinker worksheet column C equals Hi Alk and column R is between the values of ISO page H13 and K13 i want column R to fill in green. Here is what i have but it shades green only some values between and even values outside of range. =AND($C7="Hi Alk",$R7>=ISO!$H$13,$R7<=ISO!$K$13)

[bebo021999]

Try to upload a workbook instead of an image

[Tony Valko]

Well, how about that!

I used to work in a cement plant quality control lab.

Those are some high free lime values.

[frdranger25401]

Sorry i just looked and im fairly new to this site. Can you walk me through where to upload the workbook? thanks in advance.

[frdranger25401]

OK I was able to find where i attach my workbook but its not allowing me to add the file. shows a red x. File may be to large?

[frdranger25401]

OK i was able to shorten the file size and uploaded it to the original post.

[bebo021999]

Nobody like to load a file with almost 1MB. Try to remove more unnescessary sheets, delete more unuseful datas and upload again please.

[frdranger25401]

Honestly I deleted all thats unneeded and for some reason it was still nearly 1mb. Sorry but thats as small as i can get it.

[protonLeah]

Range Name	Refers to
Clinkers	=ISO!$A:$A
HiAlk	=ISO!$A$1:$R$6
LowAlk	=ISO!$A$9:$R$14

Column R (outside range(red)):

Please Login or Register  to view this content.

Green:

Please Login or Register  to view this content.

[frdranger25401]

Could a conditioning format work as well. This is sort of what i was looking for as im used to this and understand it better. Only issue is when i use this it shades cells that arnt in that range on some and dont shade some that need shaded with color. Using this conditioning format on R7 and if its met have it fill the cell red, green, yellow etc based on the cell value of ISO workpage =AND($C7="Hi Alk",$R7>=ISO!$O$13,$U7<=ISO!$Q$13)

[protonLeah]

The formulas I gave you are to be used for the conditional format formulas.

You need the named ranges because of the layout of the ISO sheet. There are two tables to look in, Low Alkali & Hi Alkali. The dropdown in column C only tells you which table to look in. You still need to know the row in the chosen table and the column.
Your dropdown choices are Hi Alk and Low Alk but you cannot have spaces in named ranges. Therefore, I named the two ranges LowAlk & HiAlk. In the formula, the

SUBSTITUTE($C7," ","")

just squeezes out the space from the choice so Low Alk becomes LowAlk (the name of the table to read from).

If you want simpler formulas you need to redo that ISO sheet, You need two separate sheets (Hi ahd Low) and you need to get rid of all those merged cells, they just make things hard and are not necessary.

[frdranger25401]

Ok Much thanks. Now that I understand what you did it makes alot of sense. Thanks for explaining.

[frdranger25401]

Solved. Thanks Again