I wish to nest more than 8 If Statements 3 times ie.

Range A1 needs to test that the value 5:-

01 - Does not appear in a Row of 9 columns - say B1:B9
02 - Does not appear in a Column of 9 rows - Say A2:A10
03 - Does not appear in an array of a 3x3 grid - Say an array named "grp1"

Is this at all possible?

The result is, if it should find a value of 5 in these locations that the result is a blank cell in A1.

Regards, Matt

You are limited to 7-deep on the if's in one cell. You can use the Match function to check for the presence of a value in a row OR column range of cells. I can't think right now how you might check a 2-d range of cells???
You can also build the ifs in segments in different cells and then unite the product in another cell.

Technically, you can nest up to 49 IF's in Excel (although you are limited to 1,024 characters in a formula), using name formulas for a set of nested IFs.

BUT, it is a nightmare! I developed a solution for someone which was nested up to 13 IFs, and it has worked for that person flawlessly for 3 years for a critical company-wide report. But the lookup table is far better, more convenient, easier to trouble-shoot, and can be expanded as far as the need.

Technically, you can nest up to 49 IF's in Excel (although you are limited to 1,024 characters in a formula), using name formulas for a set of nested IFs.

BUT, it is a nightmare! I developed a solution for someone which was nested up to 13 IFs, and it has worked for that person flawlessly for 3 years for a critical company-wide report. But the lookup table is far better, more convenient, easier to trouble-shoot, and can be expanded as far as the need.

Technially you are not correct, you can only nest 7 IFs

=IF(etc,IF(etc,IF(etc and so on

Not:

=IF(etc,IF(etc,TRUE,FALSE),IF(etc,TRUE,FALSE))
which is only 2 nested IFs in each part of the formula.

However, to the original question.

Can you please expand a little on your specific requirement as, Shades does correctly say a lookup table may be what you need.

Cheers,

I would be more than happy to use a lookup table but I was struggling with needing to check three ranges to determin the value of a cell.

i.e

If a number from 1 - 9 does not appear in 1 of 9 cells in a row &
If a number from 1 - 9 does not appear in 1 of 9 cells in a colum &
If a number from 1 - 9 does not appear in a range of cells

then

the numbers from 1 to 9 that do not appear will be indiacted in another cell or cells.

I hope that this explaination is clear.

Thank you, Matt

Sounds like SuDoku???
Why don't you do it in VBA?
If it is SuDoku, check these links...
http://www.andypope.info/fun/sudoku.htm
http://www.dicks-blog.com/

Thank you

I had thought of VBA but its going to take along time to work out the code generated on the links.

Look forward to the challenge though.

Thank you.