If(or(and problem

[davehow87]

Hi,

I posted yesterday and an initial problem was solved but now having a futher problem- can you help please!

sorry about the long formula- this formula has no errors but it's not performing the right action- i want it to only post REQUIRED or N/A in the cell if both conditions are correct- (for example, if GL5 contains the info from JOBROLES! D7 and there is a YES in JOBROLES! J7 then it should say REQUIRED- otherwise if GL5 is empty i want it to always say INPUT JOB.)

=IF(OR(AND(GL5='JOB ROLES'!D7,'JOB ROLES'!J7="YES"),AND(GL5='JOB ROLES'!D8,'JOB ROLES'!J8="YES"),AND(GL5='JOB ROLES'!D9,'JOB ROLES'!J9="YES"),AND(GL5='JOB ROLES'!D10,'JOB ROLES'!J10="YES"),AND(GL5='JOB ROLES'!D11,'JOB ROLES'!J11="YES"),AND(GL5='JOB ROLES'!D12,'JOB ROLES'!J12="YES"),AND(GL5='JOB ROLES'!D13,'JOB ROLES'!J13="YES"),AND(GL5='JOB ROLES'!D14,'JOB ROLES'!J14="YES"),AND(GL5='JOB ROLES'!D15,'JOB ROLES'!J15="YES"),AND(GL5='JOB ROLES'!D16,'JOB ROLES'!J16="YES"),AND(GL5='JOB ROLES'!D18,'JOB ROLES'!J18="YES"),AND(GL5='JOB ROLES'!D19,'JOB ROLES'!J19="YES"),AND(GL5='JOB ROLES'!D20,'JOB ROLES'!J20="YES"),AND(GL5='JOB ROLES'!D21,'JOB ROLES'!J21="YES"),AND(GL5='JOB ROLES'!D23,'JOB ROLES'!J23="YES"),AND(GL5='JOB ROLES'!D24,'JOB ROLES'!J24="YES"),AND(GL5='JOB ROLES'!D25,'JOB ROLES'!J25="YES"),AND(GL5='JOB ROLES'!D26,'JOB ROLES'!J26="YES"),AND(GL5='JOB ROLES'!D27,'JOB ROLES'!J27="YES"),AND(GL5='JOB ROLES'!D28,'JOB ROLES'!J28="YES"),AND(GL5='JOB ROLES'!D29,'JOB ROLES'!J29="YES"),AND(GL5='JOB ROLES'!D30,'JOB ROLES'!J30="YES"),AND(GL5='JOB ROLES'!D31,'JOB ROLES'!J31="YES"),AND(GL5='JOB ROLES'!D32,'JOB ROLES'!J32="YES"),AND(GL5='JOB ROLES'!D33,'JOB ROLES'!J33="YES"),AND(GL5='JOB ROLES'!D34,'JOB ROLES'!J34="YES"),AND(GL5='JOB ROLES'!D35,'JOB ROLES'!J35="YES"),AND(GL5='JOB ROLES'!D36,'JOB ROLES'!J36="YES"),AND(GL5='JOB ROLES'!D37,'JOB ROLES'!J37="YES"),AND(GL5='JOB ROLES'!D38,'JOB ROLES'!J38="YES"),AND(GL5='JOB ROLES'!D39,'JOB ROLES'!J7="YES"),AND(GL5='JOB ROLES'!D40,'JOB ROLES'!J40="YES"),AND(GL5='JOB ROLES'!D41,'JOB ROLES'!J41="YES"),AND(GL5='JOB ROLES'!D42,'JOB ROLES'!J42="YES"),AND(GL5='JOB ROLES'!D43,'JOB ROLES'!J43="YES"),AND(GL5='JOB ROLES'!D44,'JOB ROLES'!J44="YES"),AND(GL5='JOB ROLES'!D45,'JOB ROLES'!J45="YES"),AND(GL5='JOB ROLES'!D46,'JOB ROLES'!J46="YES"),AND(GL5='JOB ROLES'!D47,'JOB ROLES'!J47="YES"),AND(GL5='JOB ROLES'!D48,'JOB ROLES'!J48="YES"),AND(GL5='JOB ROLES'!D49,'JOB ROLES'!J49="YES"),AND(GL5='JOB ROLES'!D50,'JOB ROLES'!J50="YES"),AND(GL5='JOB ROLES'!D51,'JOB ROLES'!J51="YES"),AND(GL5='JOB ROLES'!D52,'JOB ROLES'!J52="YES"),AND(GL5='JOB ROLES'!D53,'JOB ROLES'!J53="YES"),AND(GL5='JOB ROLES'!D54,'JOB ROLES'!J54="YES"),AND(GL5='JOB ROLES'!D55,'JOB ROLES'!J55="YES"),AND(GL5='JOB ROLES'!D56,'JOB ROLES'!J56="YES"),AND(GL5='JOB ROLES'!D57,'JOB ROLES'!J57="YES"),AND(GL5='JOB ROLES'!D58,'JOB ROLES'!J58="YES"),AND(GL5='JOB ROLES'!D59,'JOB ROLES'!J59="YES"),AND(GL5='JOB ROLES'!D60,'JOB ROLES'!J60="YES"),AND(GL5='JOB ROLES'!D61,'JOB ROLES'!J61="YES"),AND(GL5='JOB ROLES'!D62,'JOB ROLES'!J62="YES"),AND(GL5='JOB ROLES'!D63,'JOB ROLES'!J63="YES"),AND(GL5='JOB ROLES'!D64,'JOB ROLES'!J64="YES"),AND(GL5='JOB ROLES'!D65,'JOB ROLES'!J65="YES"),AND(GL5='JOB ROLES'!D66,'JOB ROLES'!J66="YES"),AND(GL5='JOB ROLES'!D67,'JOB ROLES'!J67="YES"),AND(GL5='JOB ROLES'!D68,'JOB ROLES'!J68="YES"),AND(GL5='JOB ROLES'!D69,'JOB ROLES'!J69="YES"),AND(GL5='JOB ROLES'!D70,'JOB ROLES'!J70="YES"),AND(GL5='JOB ROLES'!D71,'JOB ROLES'!J71="YES"),AND(GL5='JOB ROLES'!D72,'JOB ROLES'!J72="YES"),AND(GL5='JOB ROLES'!D73,'JOB ROLES'!J73="YES"),AND(GL5='JOB ROLES'!D74,'JOB ROLES'!J74="YES"),AND(GL5='JOB ROLES'!D75,'JOB ROLES'!J75="YES")),"REQUIRED",IF(OR(AND(GL5='JOB ROLES'!D7,'JOB ROLES'!J7="NO"),AND(GL5='JOB ROLES'!D8,'JOB ROLES'!J8="NO"),AND(GL5='JOB ROLES'!D9,'JOB ROLES'!J9="NO"),AND(GL5='JOB ROLES'!D10,'JOB ROLES'!J10="NO"),AND(GL5='JOB ROLES'!D11,'JOB ROLES'!J11="NO"),AND(GL5='JOB ROLES'!D12,'JOB ROLES'!J12="NO"),AND(GL5='JOB ROLES'!D13,'JOB ROLES'!J13="NO"),AND(GL5='JOB ROLES'!D14,'JOB ROLES'!J14="NO"),AND(GL5='JOB ROLES'!D15,'JOB ROLES'!J15="NO"),AND(GL5='JOB ROLES'!D16,'JOB ROLES'!J16="NO"),AND(GL5='JOB ROLES'!D18,'JOB ROLES'!J18="NO"),AND(GL5='JOB ROLES'!D19,'JOB ROLES'!J19="NO"),AND(GL5='JOB ROLES'!D20,'JOB ROLES'!J20="NO"),AND(GL5='JOB ROLES'!D21,'JOB ROLES'!J21="NO"),AND(GL5='JOB ROLES'!D23,'JOB ROLES'!J23="NO"),AND(GL5='JOB ROLES'!D24,'JOB ROLES'!J24="NO"),AND(GL5='JOB ROLES'!D25,'JOB ROLES'!J25="NO"),AND(GL5='JOB ROLES'!D26,'JOB ROLES'!J26="NO"),AND(GL5='JOB ROLES'!D27,'JOB ROLES'!J27="NO"),AND(GL5='JOB ROLES'!D28,'JOB ROLES'!J28="NO"),AND(GL5='JOB ROLES'!D29,'JOB ROLES'!J29="NO"),AND(GL5='JOB ROLES'!D30,'JOB ROLES'!J30="NO"),AND(GL5='JOB ROLES'!D31,'JOB ROLES'!J31="NO"),AND(GL5='JOB ROLES'!D32,'JOB ROLES'!J32="NO"),AND(GL5='JOB ROLES'!D33,'JOB ROLES'!J33="NO"),AND(GL5='JOB ROLES'!D34,'JOB ROLES'!J34="NO"),AND(GL5='JOB ROLES'!D35,'JOB ROLES'!J35="NO"),AND(GL5='JOB ROLES'!D36,'JOB ROLES'!J36="NO"),AND(GL5='JOB ROLES'!D37,'JOB ROLES'!J37="NO"),AND(GL5='JOB ROLES'!D38,'JOB ROLES'!J38="NO"),AND(GL5='JOB ROLES'!D39,'JOB ROLES'!J7="NO"),AND(GL5='JOB ROLES'!D40,'JOB ROLES'!J40="NO"),AND(GL5='JOB ROLES'!D41,'JOB ROLES'!J41="NO"),AND(GL5='JOB ROLES'!D42,'JOB ROLES'!J42="NO"),AND(GL5='JOB ROLES'!D43,'JOB ROLES'!J43="NO"),AND(GL5='JOB ROLES'!D44,'JOB ROLES'!J44="NO"),AND(GL5='JOB ROLES'!D45,'JOB ROLES'!J45="NO"),AND(GL5='JOB ROLES'!D46,'JOB ROLES'!J46="NO"),AND(GL5='JOB ROLES'!D47,'JOB ROLES'!J47="NO"),AND(GL5='JOB ROLES'!D48,'JOB ROLES'!J48="NO"),AND(GL5='JOB ROLES'!D49,'JOB ROLES'!J49="NO"),AND(GL5='JOB ROLES'!D50,'JOB ROLES'!J50="NO"),AND(GL5='JOB ROLES'!D51,'JOB ROLES'!J51="NO"),AND(GL5='JOB ROLES'!D52,'JOB ROLES'!J52="NO"),AND(GL5='JOB ROLES'!D53,'JOB ROLES'!J53="NO"),AND(GL5='JOB ROLES'!D54,'JOB ROLES'!J54="NO"),AND(GL5='JOB ROLES'!D55,'JOB ROLES'!J55="NO"),AND(GL5='JOB ROLES'!D56,'JOB ROLES'!J56="NO"),AND(GL5='JOB ROLES'!D57,'JOB ROLES'!J57="NO"),AND(GL5='JOB ROLES'!D58,'JOB ROLES'!J58="NO"),AND(GL5='JOB ROLES'!D59,'JOB ROLES'!J59="NO"),AND(GL5='JOB ROLES'!D60,'JOB ROLES'!J60="NO"),AND(GL5='JOB ROLES'!D61,'JOB ROLES'!J61="NO"),AND(GL5='JOB ROLES'!D62,'JOB ROLES'!J62="NO"),AND(GL5='JOB ROLES'!D63,'JOB ROLES'!J63="NO"),AND(GL5='JOB ROLES'!D64,'JOB ROLES'!J64="NO"),AND(GL5='JOB ROLES'!D65,'JOB ROLES'!J65="NO"),AND(GL5='JOB ROLES'!D66,'JOB ROLES'!J66="NO"),AND(GL5='JOB ROLES'!D67,'JOB ROLES'!J67="NO"),AND(GL5='JOB ROLES'!D68,'JOB ROLES'!J68="NO"),AND(GL5='JOB ROLES'!D69,'JOB ROLES'!J69="NO"),AND(GL5='JOB ROLES'!D70,'JOB ROLES'!J70="NO"),AND(GL5='JOB ROLES'!D71,'JOB ROLES'!J71="NO"),AND(GL5='JOB ROLES'!D72,'JOB ROLES'!J72="NO"),AND(GL5='JOB ROLES'!D73,'JOB ROLES'!J73="NO"),AND(GL5='JOB ROLES'!D74,'JOB ROLES'!J74="NO"),AND(GL5='JOB ROLES'!D75,'JOB ROLES'!J75="NO")),"N/A",IF(GL5="","INPUT JOB")))


Thanks,

Dave

[BadlySpelledBuoy]

Try:

=IF(GL5="","INPUT JOB",IF(COUNTIFS('JOB ROLES'!D:D,GL5,'JOB ROLES'!J:J,"YES")>0,"Required","N/A"))

or

=IF(GL5="","INPUT JOB",IF(COUNTIFS('JOB ROLES'!D:D,GL5,'JOB ROLES'!J:J,"YES")>0,"Required",IF(COUNTIFS('JOB ROLES'!D:D,GL5,'JOB ROLES'!J:J,"NO")>0,"N/A","Error")))

[Kaper]

you did not (neither in text nor in formula) considered otherwise if GL5 not empty but not equal to any of D... (or equal but respective J.... neither NO nor YES)
check such final part:

Please Login or Register  to view this content.

[Kaper]

as for BadlySpelledBuoy proposition - the same principle:

Please Login or Register  to view this content.

[davehow87]

I will try now thanks guys

[davehow87]

Sorry that's not working the problem i have- i think is that there will always be a YES or NO in the cells within JOB ROLES! When i remove YES or NO from the cells in JOB ROLES! it then shows INPUT JOB but i won't it to this all the time- unless GL5 is filled with a job catagory- hope this makes sense.

Thanks very much!

[BadlySpelledBuoy]

And therein lies the exact reason why a sample workbook is ALWAYS a good idea.
It saves us having to make so many assumptions about what you're trying to achieve.

[Glenn Kennedy]

For something like this, posting an Excel sheet is a MUST. One look at your formula in Post 1 and I just had to get a big beer... I'm amazed at the courage of those who tried to answer!!

[BadlySpelledBuoy]

I only tried to answer after pouring a big beer too, Glenn

[Glenn Kennedy]

Oh look. My glass is empty. And it's only Monday...

[davehow87]

Haha sorry. What's the best way to post a sample sheet on here please?

[BadlySpelledBuoy]

Click on "Go Advanced" below your next reply then click on "Manage Attachments" below that post.
Should be straight forward from there.

[davehow87]

See attached file- sample sheet. Formula i'm testing is in cell gn9 on the alg tab

thanks

[davehow87]

does the link work?

[BadlySpelledBuoy]

Could you explain an instance where the current formula (or the solutions provided) are not giving the desired result, and tell us what the result should be??

There are what look like slight errors in your original lengthy formula (couple of instances where it says J7 but looks like it should say J39), but with those corrected, the solutions from Kaper and myself are doing exactly what your original formula is doing, only shorter.

[davehow87]

Copy of TRAINING MATRIX FINAL.xlsm


see attachment - this should work- basically i want GN9 on tab alg to say input job- unless GL5 is filled in with a job title- this will then read JOB Roles! sheet and check whether it's yes or no- returning required or n/a into the cell.

Thanks for staying with me on this

[BadlySpelledBuoy]

And here's your file with one of my originally suggested formulas included in cell GN9. If GL5 is empty it says "Input Job". If GL5 is not empty it will look up the specific job as and return the desired result depending on a Yes or No...

If I'm missing the whole point then let me know.

BSB.

[davehow87]

Sorry i've looked at this with a fresh head this morning and your formula works- thanks very much for the help