more levels of nesting than allowed in a formula

[Pursuit2010]

<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=773><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 581pt; HEIGHT: 153pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2481960 class=xl69 height=204 rowSpan=12 width=773 colSpan=10>IF(OR(AND($K8=0,($L8>=13)*($L8<=19),($N8>=25.0)*($N8<=30.9),AND($K8=1,($L8>=13)*($L8<=19),($N8>=35.0)*($N8<38.3),AND($K8=0,($L8>=20)*($L8<=29),($N8>=23.6)*($N8<=28.9),AND($K8=1,($L8>=20)*($L8<=29),($N8>=33)*($N8<=36.4),AND($K8=0,($L8>=30)*($L8<=39),($N8>=22.8)*($N8<=26.9),AND($K8=1,($L8>=30)*($L8<=39),($N8>=31.5)*($N8<=35.4),AND($K8=0,($L8>=40)*($L8<=49),($N8>=21.0)*($N8<=24.4),AND($K8=1,($L8>=13)*($L8<=49),($N8>=30.2)*($N8<=33.5),AND($K8=0,($L8>=50)*($L8<=59),($N8>=20.2)*($N8<=22.7),AND($K8=1,($L8>=13)*($L8<=59),($N8>=26.1)*($N8<=30.9),AND($K8=0,($L8>=60),($N8>=17.5)*($N8<=20.1),AND($K8=1,($L8>=60),($N8>=20.5)*($N8<=26.0)),"1","0")

</TD></TR>












Below is the formula I need to use but it tells me there are more levels of nesting than allowed.
What do I do to make it work?
<TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

 

[shg]

=IFERROR(MATCH(D3, {"none","fine","fine, gross, hand eye","fine, critical thinking","fine, gross","gross, hand eye","fine, hand eye","fine, critical thinking, hand eye","fine, gross, critical thinking","gross","fine, gross, critical thinking, hand eye","gross, critical thinking","gross, critical thinking, hand eye"}, 0), 0)

 

[basaue2]

That worked, thank you!!

 

[shg]

You're welcome, good luck.

 

[B-Man]

Hi,
im having some trouble and getting this error when i use this formula

=IFERROR(INDEX(Hayes!$A$2:$J$1000,MATCH(Summary!A2,Hayes!$A$2:$A$1000,0),2),IFERROR(INDEX(Rappa!$A$2:$J$1000,MATCH(Summary!A2,Rappa!$A$2:$A$1000,0),2),IFERROR(INDEX('TT EID'!$A$2:$J$1000,MATCH(Summary!A2,'TT EID'!$A$2:$A$1000,0),2),IFERROR(INDEX(Scales!$A$2:$J$1000,MATCH(Summary!A2,Scales!$A$2:$A$1000,0),2),IFERROR(INDEX(Prattley!$A$2:$J$1000,MATCH(Summary!A2,Prattley!$A$2:$A$1000,0),2),IFERROR(INDEX(Stafix!$A$2:$J$998,MATCH(Summary!A2,Stafix!$A$2:$A$998,0),2),IFERROR(INDEX(Speedrite!$A$2:$J$1000,MATCH(Summary!A2,Speedrite!$A$2:$A$1000,0),2),)))))))

how can i get this to work? or simplify it

or is there a way to index the same range but on different sheets

basically i want to type a part number into Summary!A2 and have it find it on one of those sheets then display the barcode, description etc that corresponds to it in the following cells

 

[B-Man]

i cant edit so post again
iv tried using this

=INDEX(INDIRECT("'"&My_Sheets&"'!$A$2:$J$1000"),MATCH(A2,INDIRECT("'"&My_Sheets&"'!A2:A1000"),0),2)

where i have defined a name MY_Sheets to a list of the sheet names but only one of them work if i ctrl+shift+enter it will get a different one to work?

 

[Peter_SSs]

Hi,
im having some trouble and getting this error when i use this formula

=IFERROR(INDEX(Hayes!$A$2:$J$1000,MATCH(Summary!A2,Hayes!$A$2:$A$1000,0),2),IFERROR(INDEX(Rappa!$A$2:$J$1000,MATCH([highlight]Summa ry![/highlight]A2,Rappa!$A$2:$A$1000,0),2),IFERROR(INDEX('TT EID'!$A$2:$J$1000,MATCH(Summary!A2,'TT EID'!$A$2:$A$1000,0),2),IFERROR(INDEX(Scales!$A$2:$J$1000,MATCH(Summary!A2,Scales!$A$2:$A$1000,0),2),IFERROR(INDEX(Pratt ley!$A$2:$J$1000,MATCH(Summary!A2,Prattley!$A$2:$A$1000,0),2),IFERROR(INDEX(Stafix!$A$2:$J$998,MATCH(Summary!A2,Stafix!$ A$2:$A$998,0),2),IFERROR(INDEX(Speedrite!$A$2:$J$1000,MATCH(Summary!A2,Speedrite!$A$2:$A$1000,0),2) [highlight],)[/highlight]))))))

how can i get this to work? or simplify it

or is there a way to index the same range but on different sheets

basically i want to type a part number into Summary!A2 and have it find it on one of those sheets then display the barcode, description etc that corresponds to it in the following cells

- You are using the IFERROR function in your formula. That means you must be using Excel 2007 or later.
- This thread is about the error "more levels of nesting than allowed in a formula".
- Excel 2007 and later allows 64 levels of nesting.

Conclusion: Your formula is not producing this error, but some other error, or else it is retuirning a non-error result that you are not expecting.

A couple of things to check/consider first

1. Check the sheet names carefully. It have have just been this forum's software when you posted a long formula, but note the highlighted sheet name above contains a space. Or at least when I copy/paste to my sheet there is a space. Could be similar for other sheet names.
IF this formula is on the Summary sheet, then you would best to remove every "Summary!" from the formula. In that case it is not required and can lead to unexpected errors.

2. If every section is an error, your formula gives no specific instruction about what to return. That would normally go in the formula near the end between the highlighted comma and closing parenthesis.


If this doesn't resolve your problem, then please ..
- tell us what error you are actually getting
- tell us which sheet the formula is on
- describe in words what your formula is trying to do

 

[B-Man]

- You are using the IFERROR function in your formula. That means you must be using Excel 2007 or later.
- This thread is about the error "more levels of nesting than allowed in a formula".
- Excel 2007 and later allows 64 levels of nesting.

Conclusion: Your formula is not producing this error, but some other error, or else it is retuirning a non-error result that you are not expecting.

A couple of things to check/consider first

1. Check the sheet names carefully. It have have just been this forum's software when you posted a long formula, but note the highlighted sheet name above contains a space. Or at least when I copy/paste to my sheet there is a space. Could be similar for other sheet names.
IF this formula is on the Summary sheet, then you would best to remove every "Summary!" from the formula. In that case it is not required and can lead to unexpected errors.

2. If every section is an error, your formula gives no specific instruction about what to return. That would normally go in the formula near the end between the highlighted comma and closing parenthesis.


If this doesn't resolve your problem, then please ..
- tell us what error you are actually getting
- tell us which sheet the formula is on
- describe in words what your formula is trying to do

im using excel 2010 but im editing an older file version so was getting this error.
there was no problem with the formula and it worked fine just couldnt get it compatible with the older version to hold more than 7 level nestings
if i saved it as a newer file then edited it, it worked but it had to be compatible with the older version


i ended up getting it working by editing a formula i found here Excel VLOOKUP Multiple Sheets (just hope it works with the older file type properly)

 

[Peter_SSs]

i ended up getting it working by editing a formula i found here (just hope it works with the older file type properly)
Excel VLOOKUP Multiple Sheets

Could you post the formula you ended up with so we too can see how you solved the problem?

 

[B-Man]

it started as this

=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)

all i done was create a named range and labeled it sheetlist so i didnt have to edit the formula much.
the range consisted of each cell containing a sheet name (where the name with a space i placed a ' either side of it as i picked up thats how excel referenced it when putting it into the first formula i tried)
changed A4 to A2
and changed

$A$1:$c$4

to the range i wanted

$A$2:$J$1000

also allows for easy adding of pages by only having to edit the named range sheet list
so ended up

{=VLOOKUP(A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$$A$2:$J$1000"),A2)>0),0))&"'!$A$2:$J$1000"),2,FALSE)}

with SheetList referring to the named range i created

dont ask me how it works though, i can adapt a formula to suit but thats about all.

 

[bestovem]

Is there anyone that could possibly help me get this formula to work? Any advice would be greatly appreciated!

=IF(A7=iBrain!B1,IF(iBrain!F1="Yes",(iBrain!C1/2),IF(iBrain!F1="No",iBrain!C1,IF(iBrain!F1="",iBrain!C1))))
IF(A7=iBrain!B2,IF(iBrain!F1="Yes",(iBrain!C2/2),IF(iBrain!F2="No",iBrain!C2,IF(iBrain!F2="",iBrain!C2))))
IF(A7=iBrain!B3,IF(iBrain!F1="Yes",(iBrain!C3/2),IF(iBrain!F3="No",iBrain!C3,IF(iBrain!F3="",iBrain!C3))))
IF(A7=iBrain!B4,IF(iBrain!F1="Yes",(iBrain!C4/2),IF(iBrain!F4="No",iBrain!C4,IF(iBrain!F4="",iBrain!C4))))
IF(A7=iBrain!B5,IF(iBrain!F1="Yes",(iBrain!C5/2),IF(iBrain!F5="No",iBrain!C5,IF(iBrain!F5="",iBrain!C5))))
IF(A7=iBrain!B6,IF(iBrain!F1="Yes",(iBrain!C6/2),IF(iBrain!F6="No",iBrain!C6,IF(iBrain!F6="",iBrain!C6))))
IF(A7=iBrain!B7,IF(iBrain!F1="Yes",(iBrain!C7/2),IF(iBrain!F7="No",iBrain!C7,IF(iBrain!F7="",iBrain!C7))))
IF(A7=iBrain!B8,IF(iBrain!F1="Yes",(iBrain!C8/2),IF(iBrain!F8="No",iBrain!C8,IF(iBrain!F8="",iBrain!C8))))
IF(A7=iBrain!B9,IF(iBrain!F1="Yes",(iBrain!C9/2),IF(iBrain!F9="No",iBrain!C9,IF(iBrain!F9="",iBrain!C9))))
IF(A7=iBrain!B10,IF(iBrain!F10="Yes",(iBrain!C10/2),IF(iBrain!F10="No",iBrain!C10,IF(iBrain!F10="",iBrain!C10))))
IF(A7=iBrain!B11,IF(iBrain!F11="Yes",(iBrain!C11/2),IF(iBrain!F11="No",iBrain!C11,IF(iBrain!F11="",iBrain!C11))))
IF(A7=iBrain!B12,IF(iBrain!F12="Yes",(iBrain!C12/2),IF(iBrain!F12="No",iBrain!C12,IF(iBrain!F12="",iBrain!C12))))
IF(A7=iBrain!B13,IF(iBrain!F13="Yes",(iBrain!C13/2),IF(iBrain!F13="No",iBrain!C13,IF(iBrain!F13="",iBrain!C13))))
IF(A7=iBrain!B14,IF(iBrain!F14="Yes",(iBrain!C14/2),IF(iBrain!F14="No",iBrain!C14,IF(iBrain!F14="",iBrain!C14))))
IF(A7=iBrain!B15,IF(iBrain!F15="Yes",(iBrain!C15/2),IF(iBrain!F15="No",iBrain!C15,IF(iBrain!F15="",iBrain!C15))))
IF(A7=iBrain!B16,IF(iBrain!F16="Yes",(iBrain!C16/2),IF(iBrain!F16="No",iBrain!C16,IF(iBrain!F16="",iBrain!C16))))
IF(A7=iBrain!B17,IF(iBrain!F17="Yes",(iBrain!C17/2),IF(iBrain!F17="No",iBrain!C17,IF(iBrain!F17="",iBrain!C17))))
IF(A7=iBrain!B18,IF(iBrain!F18="Yes",(iBrain!C18/2),IF(iBrain!F18="No",iBrain!C18,IF(iBrain!F18="",iBrain!C18))))
IF(A7=iBrain!B19,IF(iBrain!F19="Yes",(iBrain!C19/2),IF(iBrain!F19="No",iBrain!C19,IF(iBrain!F19="",iBrain!C19))))
IF(A7=iBrain!B20,IF(iBrain!F20="Yes",(iBrain!C20/2),IF(iBrain!F20="No",iBrain!C20,IF(iBrain!F20="",iBrain!C20))))
IF(A7=iBrain!B21,IF(iBrain!F21="Yes",(iBrain!C21/2),IF(iBrain!F21="No",iBrain!C21,IF(iBrain!F21="",iBrain!C21))))
IF(A7=iBrain!B22,IF(iBrain!F22="Yes",(iBrain!C22/2),IF(iBrain!F22="No",iBrain!C22,IF(iBrain!F22="",iBrain!C22))))
IF(A7=iBrain!B23,IF(iBrain!F23="Yes",(iBrain!C23/2),IF(iBrain!F23="No",iBrain!C23,IF(iBrain!F23="",iBrain!C23))))
IF(A7=iBrain!B24,IF(iBrain!F24="Yes",(iBrain!C24/2),IF(iBrain!F24="No",iBrain!C24,IF(iBrain!F24="",iBrain!C24))))
IF(A7=iBrain!B25,IF(iBrain!F25="Yes",(iBrain!C25/2),IF(iBrain!F25="No",iBrain!C25,IF(iBrain!F25="",iBrain!C25))))
IF(A7=iBrain!B26,IF(iBrain!F26="Yes",(iBrain!C26/2),IF(iBrain!F26="No",iBrain!C26,IF(iBrain!F26="",iBrain!C26))))
IF(A7=iBrain!B27,IF(iBrain!F27="Yes",(iBrain!C27/2),IF(iBrain!F27="No",iBrain!C27,IF(iBrain!F27="",iBrain!C27))))
IF(A7=iBrain!B28,IF(iBrain!F28="Yes",(iBrain!C28/2),IF(iBrain!F28="No",iBrain!C28,IF(iBrain!F28="",iBrain!C28))))
IF(A7=iBrain!B29,IF(iBrain!F29="Yes",(iBrain!C29/2),IF(iBrain!F29="No",iBrain!C29,IF(iBrain!F29="",iBrain!C29))))
IF(A7=iBrain!B30,IF(iBrain!F30="Yes",(iBrain!C30/2),IF(iBrain!F30="No",iBrain!C30,IF(iBrain!F30="",iBrain!C30))))