Limitation of Excel only allowing up to 7 nested functions.

[smk7997]

The limitation within Excel of only allowing up to 7 nested functions is very
fustrating when you are dealing with complex functions and worksheets. Is
there any way around this or does Microsoft plan on changing this limitation
to allow larger functions?

 

[Bob Phillips]

Could be plenty of ways.

Give details of your data and what you are trying to do.

 

[BenjieLop]

Look at your data and perhaps you can put them in a tabular form. if so
you can use the VLOOKUP function.

 

[veganfury]

I have little experiance with Excel, but while currently unemployed I am
attempting to create a grade/attendance book for a teacher friend. I am
trying to create a work sheet that has a drop down list of all students
names once they are entered into the primary worksheet. Once a name is
selected from the dropdown I want the worksheet to display information
from the 2 previous worksheets.

I am using the "if" function to write an argument for each student.
unfortunately I get stopped at 7 functions and there are 40+ students
in each class. it could be that i just need to arrange the data so
that a different funtion can be used, i don't know - like I said i have
little experience with Excel.

I and a bunch of teachers at a non-profit charter school thank you.

Matt


+-------------------------------------------------------------------+
|Filename: work in progress.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2686 |
+-------------------------------------------------------------------+

 

[Myrna Larson]

You should be able to solve this by setting up a table to use with VLOOKUP.
Student names would be in 1st column.

 

[JulieD]

Check out Debra Dalgleish's site for information on data validation &
vlookup
http://www.contextures.com/tiptech.html

Cheers
JulieD

 

[Biff]

Hi Matt!

Just my opinion - I would make all the tables vertical.

On your Report 1 sheet in cell D20 enter this formula and
copy across as needed:

=IF($A2="","",VLOOKUP($A2,$A41:$FZ78,COLUMN(AC:AC),0))

In cell D36 enter this formula and copy across as needed:

=IF($A2="","",VLOOKUP($A2,$A41:$Z78,COLUMN(D),0))

Biff

-----Original Message-----

I have little experiance with Excel, but while currently unemployed I am
attempting to create a grade/attendance book for a teacher friend. I am
trying to create a work sheet that has a drop down list of all students
names once they are entered into the primary worksheet. Once a name is
selected from the dropdown I want the worksheet to display information
from the 2 previous worksheets.

I am using the "if" function to write an argument for each student.
unfortunately I get stopped at 7 functions and there are 40+ students
in each class. it could be that i just need to arrange the data so
that a different funtion can be used, i don't know - like I said i have
little experience with Excel.

I and a bunch of teachers at a non-profit charter school thank you.

Matt


+--------------------------------------------------------- ----------+
|Filename: work in

progress.zip |

 

[Dave Peterson]

Direct links (although snooping around Debra's site would be a good thing!):

Data validation:
http://www.contextures.com/xlDataVal01.html

=vlookup():
http://www.contextures.com/xlFunctions02.html

 

[veganfury]

I would like to thank everyone in this group for their help and suppor
with my questions, I appreciate all of your help. Not only did yo
enable me to make the program do what I wanted it to do, but also gav
me several good ideas to make improvements.

thanks so much!

Mat