Yes or no answer depending on criteria

[Garetht2014]

I am trying to write a formula that is based on 2 criteria, depending it is meets that critieria it will wither give me a Yes or No answer, but not really getting anywhere with it.

What I am trying to do it get the 'Work Saturday or not' Column to populate with a 'Yes' or 'No' based on 2 things (ELF & PROD). If Engineer 1 has a Total ELF score of 3% or less and a Total Comp % of 75% or more, then it will populate the 'Work Saturday or not column' with a Yes.

If an engineer does not meet a Total ELF score of 3% or lower or a prod Total Comp % of 78% or higher then the 'Work Saturday or not' will be a No.

Hope all that makes sense!

I have 3 Worksheets:

(Saturday worksheet)

ELF Data (ELF worksheet)

PROD Data (Prod worksheet)

 

[Legacy 143009]

What if Total ELF score is higher than 3% or Total Comp is somewhere in between 75% and 78%?

 

[Garetht2014]

Hi Flashbond,

If the ELF score is above 3% then I need that to be a No and the same for the Total Comp, if they are below 78% it would be a no

 

[pmcgrath92]

=IF(AND([elfcell]<=3,[prodcell]>=75),"YES",IF(OR([elfcell]>3,[prodcell]<=78),"NO","ERROR"))

 

[Garetht2014]

Thanks PMC, but unfortunately that didn't work, it just gave a No for everyone.

One thing I missed off before, is the formula needs to do a look up on the name from the Saturday Worksheet so it looks at the correct scores on the other sheets!

 

[Legacy 143009]

For the following setup, your D1 formula will be:

=IF(AND(VLOOKUP(A1,$A$7:$H$10,8,0)>3%,VLOOKUP(A1,$A$13:$O$16,15,0)<78%),"No","Yes")

 

[Garetht2014]

But my ELF and PROD are on seperate worksheet ?

 

[Garetht2014]

Your formula above will only work if all the data is on the same worksheet

 

[pmcgrath92]

Hey Gareth,

Simply change the references to select the data in the other worksheet.

 

[Garetht2014]

I did try to that but I have no clue how to write it in there to make it look at the other sheets ?