I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

Thanx JMay,

Can u pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d",""))))

"JMay" wrote:

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?

Sorry, late for work here -- maybe someone else can jump in...


"FARAZ QURESHI" wrote:

Thanx JMay,

Can u pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d",""))))

"JMay" wrote:

You're going to have to write a Macro to do what you want.
It will involve looping thru your range of data.

"FARAZ QURESHI" wrote:

I have quite a number of columns to be checked in various criteria.
Upon first criteria not matched the helper cell is to return "1", upon the
second, "2".
However, I have come to know that I won't be able to enter more than 7
nested functions e.g.:

=IF(ISNA(VLOOKUP(D3,BRANCH,1,FALSE)),"1",IF(ISNA(V LOOKUP(E3,SEGMENT,1,FALSE)),"2",IF(OR(NOT(ISNUMBER (G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN,1,FALSE) ))),"3",IF(OR(NOT(ISNUMBER(F3)),F3<100,F3999,NOT( ISNA(VLOOKUP(F3,XON,1,FALSE)))),"4",IF(AND((OR(NOT (ISNUMBER(G3)),G3<10,G399,NOT(ISNA(VLOOKUP(G3,XNN ,1,FALSE))))),(OR(NOT(ISNUMBER(F3)),F3<100,F3999, NOT(ISNA(VLOOKUP(F3,XON,1,FALSE)))))),"5",IF(OR(NO T(ISNUMBER(H3)),H3<0),"6",""))))))

Any idea how to have my work completed and compile the formula results to be
reflected in the same cell?