On Tue, 14 Jun 2016 00:56:41 -0700 (PDT),
wrote:

Vào 03:49:00 UTC+7 Th? Ba, ngày 19 tháng 8 n?m 2008, trexcel ?ã vi?t:
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.

Try separate the formula into a few ones and then rejoin them.

First:
1. I don't get the error in Excel 2010.
2. There may be a typo in your formula: IF(A1=9,U1:U10,0)
I would use "IF(A1=9,U1,0)" instead of using the *range* "U1:U10" as a
result. It seems to work as intended anyway, but it seems unnecessary
to use a *range* here.

One alternative: Create a user-defined function in VBA.

Or use an extra column, and get the final result from C1:

B1: =IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,0.99)))))
C1: =IF(B1<0.99,B1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,I F(A1=9,U1,0)))))

Using the value of "0.99" as a flag.

The flag could be text if you want to use the 'exact" function for the
test in C1, though the test would need to be equivalent to
"=if(not(exact(B1,"flag")),...".