On Wed, 2 Oct 2013 01:33:54 -0700 (PDT), wrote:

I am trying to create an IF formula that I can later paste into data
validation.
get the following error:

The specified formula cannot be entered because it uses more than ^$ levels of
nesting. here is mu Formula


Below is your formula re-written with line feeds so as to be able to interpret it better:

It seems you are testing forthree different conditions
e.g: B1&B2
B2&B1
B5+B4

And you want to test in a particular order.

In addition, there is no regular relationship between the cell addresses of either the values your are checking, or the return values.

If you have a version of Excel prior to 2007, the nesting limit is 7; if it is 2007 or later, the nesting limit is 64 and this formula exceeds that.

One way to handle this would be to split this formula into multiple cells.
If you don't have to worry about versions prior to 2007, you could split where the condition changes, and then return the value of the first cell that returns a non-FALSE

eg

A1: =IF($B$1&$B$2=Control!$J$18,'FY16'!$CD9)

A2: =IF($B$2&$B$1=Control!$J$19,'FY16'!$BN9,
IF($B$2&$B$1=Control!$J$20,'FY16'!$BP9,
IF($B$2&$B$1=Control!$J$21,'FY16'!$BR9,
IF($B$2&$B$1=Control!$J$22,'FY16'!$BT9,
IF($B$2&$B$1=Control!$J$23,'FY16'!$CB9,
IF($B$2&$B$1=Control!$J$24,'FY16'!$BS9,
IF($B$2&$B$1=Control!$J$26,'FY16'!$BV9,
IF($B$2&$B$1=Control!$J$27,'FY16'!$BW9,
IF($B$2&$B$1=Control!$J$28,'FY16'!$BY9,
IF($B$2&$B$1=Control!$J$29,'FY16'!$BX9,
IF($B$2&$B$1=Control!$J$30,'FY16'!$BO9,
IF($B$2&$B$1=Control!$J$31,'FY16'!$BQ9,
IF($B$2&$B$1=Control!$J$32,'FY16'!$CA9,
IF($B$2&$B$1=Control!$J$33,'FY16'!$CC9,
IF($B$2&$B$1=Control!$J$34,'FY16'!$BZ9,
IF($B$2&$B$1=Control!$J$35,'FY16'!$BU9) <-- enough )'s to close

A3: =IF($B$5+$B$4=10,'FY16'!$AB9)

A4: =IF($B$2&$B$1=Control!$J$37,'FY16'!$L9,
IF($B$2&$B$1=Control!$J$38,'FY16'!$N9,
IF($B$2&$B$1=Control!$J$39,'FY16'!$P9,
IF($B$2&$B$1=Control!$J$40,'FY16'!$R9,
IF($B$2&$B$1=Control!$J$41,'FY16'!$Z9,
IF($B$2&$B$1=Control!$J$42,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$46,'FY16'!$W9,
IF($B$2&$B$1=Control!$J$44,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$45,'FY16'!$U9,
IF($B$2&$B$1=Control!$J$47,'FY16'!$V9,
IF($B$2&$B$1=Control!$J$48,'FY16'!$M9,
IF($B$2&$B$1=Control!$J$49,'FY16'!$O9,
IF($B$2&$B$1=Control!$J$50,'FY16'!$Y9,
IF($B$2&$B$1=Control!$J$51,'FY16'!$AA9,
IF($B$2&$B$1=Control!$J$52,'FY16'!$X9,
IF($B$2&$B$1=Control!$J$53,'FY16'!$S9) <-- enough )'s to close

etc.

Then you can use a formula like:

This formula must be **array-entered**:

=INDEX($A$1:$A$10,MATCH(TRUE,A1:A10<FALSE,0))

(replace A1:A10 with the range where you have entered your series of formulas)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


IF you need to deal with versions prior to 2007, then you will have to split the IF sequences into smaller chunks.

Another method of handling this would be to write a User Defined Function in VBA, but I think you can just break up your formula.


-----------------------------------------------------------------------
IF($B$1&$B$2=Control!$J$18,'FY16'!$CD9,
IF($B$2&$B$1=Control!$J$19,'FY16'!$BN9,
IF($B$2&$B$1=Control!$J$20,'FY16'!$BP9,
IF($B$2&$B$1=Control!$J$21,'FY16'!$BR9,
IF($B$2&$B$1=Control!$J$22,'FY16'!$BT9,
IF($B$2&$B$1=Control!$J$23,'FY16'!$CB9,
IF($B$2&$B$1=Control!$J$24,'FY16'!$BS9,
IF($B$2&$B$1=Control!$J$26,'FY16'!$BV9,
IF($B$2&$B$1=Control!$J$27,'FY16'!$BW9,
IF($B$2&$B$1=Control!$J$28,'FY16'!$BY9,
IF($B$2&$B$1=Control!$J$29,'FY16'!$BX9,
IF($B$2&$B$1=Control!$J$30,'FY16'!$BO9,
IF($B$2&$B$1=Control!$J$31,'FY16'!$BQ9,
IF($B$2&$B$1=Control!$J$32,'FY16'!$CA9,
IF($B$2&$B$1=Control!$J$33,'FY16'!$CC9,
IF($B$2&$B$1=Control!$J$34,'FY16'!$BZ9,
IF($B$2&$B$1=Control!$J$35,'FY16'!$BU9,
IF($B$5+$B$4=10,'FY16'!$AB9,
IF($B$2&$B$1=Control!$J$37,'FY16'!$L9,
IF($B$2&$B$1=Control!$J$38,'FY16'!$N9,
IF($B$2&$B$1=Control!$J$39,'FY16'!$P9,
IF($B$2&$B$1=Control!$J$40,'FY16'!$R9,
IF($B$2&$B$1=Control!$J$41,'FY16'!$Z9,
IF($B$2&$B$1=Control!$J$42,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$46,'FY16'!$W9,
IF($B$2&$B$1=Control!$J$44,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$45,'FY16'!$U9,
IF($B$2&$B$1=Control!$J$47,'FY16'!$V9,
IF($B$2&$B$1=Control!$J$48,'FY16'!$M9,
IF($B$2&$B$1=Control!$J$49,'FY16'!$O9,
IF($B$2&$B$1=Control!$J$50,'FY16'!$Y9,
IF($B$2&$B$1=Control!$J$51,'FY16'!$AA9,
IF($B$2&$B$1=Control!$J$52,'FY16'!$X9,
IF($B$2&$B$1=Control!$J$53,'FY16'!$S9,
IF($B$5+$B$4=20,'FY16'!$AT9,
IF($B$2&$B$1=Control!$M$19,'FY16'!$AD9,
IF($B$2&$B$1=Control!$M$20,'FY16'!$AF9,
IF($B$2&$B$1=Control!$M$21,'FY16'!$AH9,
IF($B$2&$B$1=Control!$M$22,'FY16'!$AJ9,
IF($B$2&$B$1=Control!$M$23,'FY16'!$AR9,
IF($B$2&$B$1=Control!$M$24,'FY16'!$AI9,
IF($B$2&$B$1=Control!$M$26,'FY16'!$AL9,
IF($B$2&$B$1=Control!$M$27,'FY16'!$AM9,
IF($B$2&$B$1=Control!$M$28,'FY16'!$AO9,
IF($B$2&$B$1=Control!$M$29,'FY16'!$AN9,
IF($B$2&$B$1=Control!$M$30,'FY16'!$AE9,
IF($B$2&$B$1=Control!$M$31,'FY16'!$AG9,
IF($B$2&$B$1=Control!$M$32,'FY16'!$AQ9,
IF($B$2&$B$1=Control!$M$33,'FY16'!$AS9,
IF($B$2&$B$1=Control!$M$34,'FY16'!$AP9,
IF($B$2&$B$1=Control!$M$35,'FY16'!$AP9,
IF($B$5+$B$4=30,'FY16'!$BL9,
IF($B$2&$B$1=Control!$M$37,'FY16'!$AV9,
IF($B$2&$B$1=Control!$M$38,'FY16'!$AX9,
IF($B$2&$B$1=Control!$M$39,'FY16'!$AZ9,
IF($B$2&$B$1=Control!$M$40,'FY16'!$BB9,
IF($B$2&$B$1=Control!$M$41,'FY16'!$BJ9,
IF($B$2&$B$1=Control!$M$42,'FY16'!$BA9,
IF($B$2&$B$1=Control!$M$44,'FY16'!$BD9,
IF($B$2&$B$1=Control!$M$45,'FY16'!$BE9,
IF($B$2&$B$1=Control!$M$46,'FY16'!$BG9,
IF($B$2&$B$1=Control!$M$47,'FY16'!$BF9,
IF($B$2&$B$1=Control!$M$48,'FY16'!$AW9,
IF($B$2&$B$1=Control!$M$49,'FY16'!$AY9,
IF($B$2&$B$1=Control!$M$50,'FY16'!$BI9,
IF($B$2&$B$1=Control!$M$51,'FY16'!$BK9,"0")))))))) )))))))))))))))))))))))))))))))))))))))))))))))))) ))))))))