Conditional Formatting based on 2 sets of criteria?

[Ian1976]

Hi,

I'm using this formula...

=IFERROR(VLOOKUP(AG3,A2:T89,200)," ")

Is there a way to also check if the text in AG3 starts with 319 and the result returned from A2:T89 was >499 then colour the result if conditons are met in red?

Thanks

 

[Fluff]

I don't understand what you are asking.
No part of your formula affects the range Z19:AU28

 

[Ian1976]

Hi Fluff,

Sorry it's complicated (to me, anyway!) this is the conditional formula i'm using in cell AG19, =AND(LEFT(AG3,3)="319",IFERROR(VLOOKUP(AG3,A2:T89,20,0),0)<=-300)

it checks AG3 to see if it starts with 319 then if it does it checks if the results in the bottom table are <= -400 and if thats true it colours the cell pink at the moment, i was going to just use a simple conditional formula but have already used one for all the other cells starting with 350 if they are <= -800 they turn red!

do you need anymore info?

I'm trying to upload a screen shot of my sheet but im struggling at the moment!

Thanks!

 

[Fluff]

I'm now even more confused.
Please explain EXACTLY what you are trying todo.
For instance are you tying to colour an individual cell, or entire rows?
Should every cell be looking at AG3, or does that need to be dynamic?

 

[Ian1976]

https://1drv.ms/x/s!Aszg_mMOoSFEgRp_a_jfxg505iGP

Heres the file im working on, hopefully my explanation will now make more sense, i am trying to colour in every cell that starts with 319 when the hours get below -400, ive used conditional formatting to get the 350's to show in red when they get below -800!

so if the top table has a cell with 319 in it and the coresponding hours in the table below are -400 then i'd like it to be hightlighted in red!

thanks
Ian

 

[Fluff]

Ok, select Z19:AU28 & use this formula
=AND(LEFT(Z3,3)="319", Z19<=-400)

 

[Ian1976]

Thanks Fluff, works perfectly! Can you explain/break it down for me when you have time?

Thanks

 

[Fluff]

It's simply saying if a cell in the top section starts with 319 & the corresponding cell (ie 16 rows below) is less than or equal to -400 then highlight it

 

[Ian1976]

Thank you! ?

 

[Fluff]

You're welcome

 

[Ian1976]

Hi Fluff,

HNY!

https://1drv.ms/x/s!Aszg_mMOoSFEgRyt-qUa9uRsOkZG

Could you have a look at my spreadsheet and tell me if there is a way of keeping the formulas in the same order as the rest of the data after sorting it using the buttons?

I had a play with changing the formulas to absolute but can't figure out where I'm going wrong?

Thanks
Ian