Excel nested issue

[karron]

On a monthly basis a sheet of data is inserted into a different worksheet, each worksheet named after the month.
What I want to do is check to see if the data is in these 12 pre-named worksheets and place a value in the cell, like the following:

=IF(AugPerf!A2>0,8,IF(JulPerf!A2>0,7,IF(JunPerf!A2>0,6,IF(MayPerf!A2>0,5,IF(AprPerf!A2>0,4,IF(MarPerf!A2>0,3,IF(FebPerf!A2>0,2,IF(JanPerf!A2>0,1,0))))))))

However, as you will be aware you cannot nest the if for more than 8 I think, can you help as to how i progress this one.

 

[cheerio]

Take a look at the AND function

 

[karron]

If i use the AND function with the if is that not for two criteria for example if it is <>then enter this, how do i use it for for what i am doing when i want a unique number depending on each individual criteria?

 

[GlennUK]

You could generate a number for each test, having each one exponentially higher than the next. Then do some fudging with the results and you've got the answer you want. This is the kind of formula you need ...

=INT(LOG10(IF(JanPerf!A2>0,1e1,0)+IF(FebPerf!A2>0,1e2,0)+IF(MarPerf!A2>0,1e3,0)+IF(AprPerf!A2>0,1e4,0)+IF(MayPerf!A2>0,1e5,0)+IF(JunPerf!A2>0,1e6,0)+IF(JulPerf!A2>0,1e7,0)+IF(AugPerf!A2>0,1e8,0)+IF(SepPerf!A2>0,1e9,0)+IF(OctPerf!A2>0,1e10,0)+IF(NovPerf!A2>0,1e11,0)+IF(DecPerf!A2>0,1e12,0)))

Glenn.

 

[karron]

thankyou very much that is perfect!