Nested Function Limit 7

[spoko]

Hello



How can I overcome limit of seven in nested function? I know this looks ridicules
but in order to make this work for me it has to be that way. Any help would be greatly appreciated .
Also I have 40 check boxes in worksheet1 only some of them are checked at one time (10 or 15 at the most ) , how can I do a printout in worksheet2 of only what is checked in worksheet1, linking cells is not working for me because some items (worksheet1)
have multiple choices, so if one of them is selected I want it to be displayed in the same cell. Hope this is clear enough.
Thanks


=IF(AND(Sheet1!A10=TRUE,Sheet1!A20),"single door hinge left",IF(AND(Sheet1!A10=TRUE,Sheet1!B20=TRUE),"single door hinge right",IF(AND(Sheet1!E10=TRUE,Sheet1!A20=TRUE),"Double Door Hinge Left",IF(AND(Sheet1!E10=TRUE,Sheet1!B20=TRUE),"Double Door Hinge Right",IF(AND(Sheet1!B10=TRUE,Sheet1!A20=TRUE),"Door Sidelite Hinge Left",IF(AND(Sheet1!B10=TRUE,Sheet1!C20=TRUE,Sheet1!B20=TRUE),"Door Sidelite Centre Post Hinge Right",IF(AND(Sheet1!C10=TRUE,Sheet1!B20=TRUE),"Sidelite Door Hinge Right",=IF(AND(Sheet1!A10=TRUE,Sheet1!A20),"single door hinge left",IF(AND(Sheet1!A10=TRUE,Sheet1!B20=TRUE),"single door hinge right",IF(AND(Sheet1!E10=TRUE,Sheet1!A20=TRUE),"Double Door Hinge Left",IF(AND(Sheet1!E10=TRUE,Sheet1!B20=TRUE),"Double Door Hinge Right",IF(AND(Sheet1!B10=TRUE,Sheet1!A20=TRUE),"Door Sidelite Hinge Left",IF(AND(Sheet1!B10=TRUE,Sheet1!C20=TRUE,Sheet1!B20=TRUE),"Door Sidelite Centre Post Hinge Right",IF(AND(Sheet1!C10=TRUE,Sheet1!B20=TRUE),"Sidelite Door Hinge Right",=IF(AND(Sheet1!B12=TRUE,Sheet1!C20=TRUE,Sheet1!A20=TRUE),"Garden Door Centre Hinge Left",IF(AND(Sheet1!B12=TRUE,Sheet1!C20=TRUE,Sheet1!B20=TRUE),"Garden Door Centre Hinge Right",IF(AND(Sheet1!B12=TRUE,Sheet1!D20=TRUE,Sheet1!A20=TRUE),"Garden Door Double Operator Hinge Left",IF(AND(Sheet1!B12=TRUE,Sheet1!D20=TRUE,Sheet1!B20=TRUE),"Garden Door Double Operator Hinge Right"

[martindwilson]

make the seventh ones 'false' reference as another cell then start again where you left of in that cell.
eg
in b1
=IF(A1="blue","yippe",IF(A1="red","wow",IF(A1="white","oops",IF(A1="yellow","cool",IF(A1="cat","meow",IF(A1="dog","woof",IF(A1="cow","moo",C1)))))))
in c1
=IF(A1="snake","hiss",IF(A1="frog","croak", -------and so on,d1)))))))
then start again in d1
but a look up of some sort would be better

[mikerickson]

Instead of hard coding all your inventory items(?) into the formula, you could use a lookup table.

If all the inventory items are in a column on Sheet2! column D and
If the 40 checkboxes are linked to A1:A40

Put this in B1
=A1*(2^1) + A2*(2^2) + A3*(2^3) +....+ A39*(2^39) + A40*(2^40) + 1

And use =INDEX(Sheet2!$D:$D,B1,1) to replace the monster IF.

[pinmaster]

Hi,

Here's another possible way to do it without the "monster" if. You could assign a different letter to each checkbox. Start by creating a 2 column table, in the second column list all the different door type, leave the first column blank for now. Next in a blank column use something like this:

=IF(A10=TRUE,"A","")..first checkbox
=IF(A11=TRUE,"B","")...second checkbox
=IF(A36=TRUE,"Z","")...26th checkbox
=IF(A37=TRUE,"AA","")...27th checkbox.... after "Z" you'll need double letters so as not to get duplicate ID codes.
continue with double letters until the last checkbox, now if you concatenate that column you'll get a unique ID which you can associate with a particular door type, so let's say checkbox 1 (A10) and checkbox 10 (A20) are checked you'll get an ID code of "AJ", now all you have to do is associate "AJ" to the correct door type (first column of the table), when that is done you can use a VLOOKUP function on the concatenated cell to get the correct door. I would use an error trap in the formula for non existant ID codes:

=IF(ISERROR(VLOOKUP(B1,your_table,2,0)),"unavailable",VLOOKUP(B1,your_table,2,0))

Hope this helps!
Jean-Guy

[Leith Ross]

Hello,

I have closed this post because the rules that posting members are supposed to follow are not being followed. Namely, wrapping your code. When you decide to follow the forums, contact me by private message, and I will unlock this post. Please re-read the rules...

Forum Rules

Sincerely,
Leith Ross