This is the code I have on Worksheet "Iso Register"
[TABLE="class: grid, width: 332"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Rev[/TD]
[TD][/TD]
[TD]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to do is report a Y or N in cell S4.
A Y would report if for example Revision is 0 and Admin Lock is True. But If Revision is 0 and is Admin False report N.
=IF(ISBLANK($A4),"",IF($T4="Y","",IF(ISERROR(VLOOKUP($E4,Rev_Criteria,1,FALSE)),"Y",IF(VLOOKUP($J4,Rev_Criteria,2,FALSE)=$J4,"Y","N"))))
Rev_Criteria refers to =OFFSET('Rev Criteria'!$A$1,0,0,COUNTA('Rev Criteria'!$A:$A),COUNTA('Rev Criteria'!$1:$1))
Rev Criteria - Worksheet
[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="width: 64"]Revision[/TD]
[TD="width: 64"]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]F[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD]J[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD]K[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
If you need any more information let me know. I'm desperate to solve this.
[TABLE="class: grid, width: 332"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Rev[/TD]
[TD][/TD]
[TD]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to do is report a Y or N in cell S4.
A Y would report if for example Revision is 0 and Admin Lock is True. But If Revision is 0 and is Admin False report N.
=IF(ISBLANK($A4),"",IF($T4="Y","",IF(ISERROR(VLOOKUP($E4,Rev_Criteria,1,FALSE)),"Y",IF(VLOOKUP($J4,Rev_Criteria,2,FALSE)=$J4,"Y","N"))))
Rev_Criteria refers to =OFFSET('Rev Criteria'!$A$1,0,0,COUNTA('Rev Criteria'!$A:$A),COUNTA('Rev Criteria'!$1:$1))
Rev Criteria - Worksheet
[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="width: 64"]Revision[/TD]
[TD="width: 64"]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]F[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD]J[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD]K[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
If you need any more information let me know. I'm desperate to solve this.