The specified Formula cannot be entered.

**Fanuelsen** · 01-20-2012, 08:04 AM

Hey, Having some problem sovling this forumla. I asked around everywhere, until I saw that this site answered problems like this

The error is: The specified Formula cannot be entered because it uses more levels of nesting then are allowed in the current fil format.

The Formula:

=IF('Competency Level'!$C$2=Level!C2;Level!C4;IF('Competency Level'!$C$2=Level!D2;Level!D4;IF('Competency Level'!$C$2=Level!E2;Level!E4;IF('Competency Level'!$C$2=Level!F2;Level!F4;IF('Competency Level'!$C$2=Level!G2;Level!G4;IF('Competency Level'!$C$2=Level!H2;Level!H4;IF('Competency Level'!$C$2=Level!I2;Level!I4;IF('Competency Level'!$C$2=Level!J2;Level!J4;IF('Competency Level'!$C$2=Level!K2;Level!K4

))))))))

-Smil

**OnErrorGoto0** · 01-20-2012, 08:21 AM

I guess you are using an xls file, in which case you can only nest 7 levels of formulas.

Simpler to use

=INDEX(Level!C4:K4;MATCH('Competency Level'!$C$2,Level!C2:K2;0))

**Fanuelsen** · 01-20-2012, 08:39 AM

Sorry but I don't see how this would help in any way.

Could you explane how I could use this =INDEX?

In my formula, I want the "auto fill in" when the diffrent "Data Validation" is selected.
Is there any way for this =IF to work in a dif

**OnErrorGoto0** · 01-20-2012, 08:42 AM

As far as I can see that formula does exactly what your nested IFs are trying to do. In what way does it not work/help?

**Fanuelsen** · 01-23-2012, 02:57 AM

When I copi the formula, it says that it dosn't work :/

**ConneXionLost** · 01-23-2012, 03:14 AM

Since it seems you are doing a horizontal search, try modifying OnError's formula like this:

=INDEX(Level!C4:K4;1;MATCH('Competency Level'!$C$2;Level!C2:K2;0))

Cheers,

**Fanuelsen** · 01-23-2012, 03:54 AM

That one seems to work perfectly

Thanks to both of you

Now to the question that would ease my pain: How do I copy this to the rest of the field? Im use to just special copy it, but it dosn't work to good here