Solved

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

Posted on 2010-08-17
4
2,817 Views
Last Modified: 2012-08-14
I am not too familiar with excel and am trying to create a spreadsheet using existing formulas.  However I get the message "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".  I do not get this error when using excel 2010 however if I try to save it as a .xls file I get the above error.  For the sample formula I have included how would you rewrite this so I do not receive the error.
=IF(F79=1,'Take Off'!E120,IF(F79=2,'Take Off'!E121,IF(F79=3,'Take Off'!E122,IF(F79=4,'Take Off'!E123,IF(F79=5,'Take Off'!E124,IF(F79=6,'Take Off'!E125,IF(F79=7,'Take Off'!E126,IF(F79=8,'Take Off'!E127,IF(F79=9,'Take Off'!E128,0)))))))))

Open in new window

0
Comment
Question by:Shumai1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33461157
Each version of Excel has a maximum level of nesting of formulas, 2010 obviously more than 2003 (.xls format).
You need to think of another way to write the formula.
I believe in 2003, it is 10 levels of nesting.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33461175
=IF(AND(F79>=1,F79<=9),INDEX('Take Off'!E120:E128, F79))
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33461177
FYI - it's actually 7 levels for 2003, 64 from 2007 up.
0
 
LVL 81

Expert Comment

by:byundt
ID: 33461223
If you want a value of 0 rather than FALSE if F79 is not 1 through 9, then consider:
=IF(ISNA(MATCH(F79,ROW($1:$9),0)),0,INDEX('Take Off'!E120:E128,F79))
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question