how can I exceed the nested if fuction limit
 

I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?

Usually the best alternative is to use a Lookup function, however, without
more details its hard to give a fuller explanation.

_____________________
Naz
London


"mgdye" wrote:

I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?

I have a drop-down listed validation for a main category of expenses in one
column, starting in cell F7. There are eleven categories: Automobile, Bills,
etc. Then in the folowing column, I want to drop-down validated list to be
dependant on the previous column's value to present sub-category choices. So
if I chose Automobile in Cell F7, I want the drop-down list in cell G7 to
display sub-categories related only to automobiles, such as Repairs, Gas,
etc. and not to show any other sub cateries for other main categories.

I have figured out how to do this by creating a table with the first column
being the main categories and then making A1's validation equal that cell.
So if the first cell in the table is K7, then I have Automobile in that cell,
Bills below it, etc and my validation for F7 is:=$K$7:$K17.

But then when I use the formula for validation list in the the next column
(starting in cell G7), there will be too many nested if functions to be able
to have sub-category options for all 11 main categories. I have the
sub-categories in the same table as mentioned in the paragraph above to the
right of the main categories so that I can use th following function:

=IF(f7=$L$7,$L$7:$n$7,if(f7=$K$8,$L$8:$U$8€¦

"Naz" wrote:

Usually the best alternative is to use a Lookup function, however, without
more details its hard to give a fuller explanation.

_____________________
Naz
London


"mgdye" wrote:

I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?

You can create dependent data validation lists. There are instructions he

http://www.contextures.com/xlDataVal02.html


mgdye wrote:
I have a drop-down listed validation for a main category of expenses in one
column, starting in cell F7. There are eleven categories: Automobile, Bills,
etc. Then in the folowing column, I want to drop-down validated list to be
dependant on the previous column's value to present sub-category choices. So
if I chose Automobile in Cell F7, I want the drop-down list in cell G7 to
display sub-categories related only to automobiles, such as Repairs, Gas,
etc. and not to show any other sub cateries for other main categories.

I have figured out how to do this by creating a table with the first column
being the main categories and then making A1's validation equal that cell.
So if the first cell in the table is K7, then I have Automobile in that cell,
Bills below it, etc and my validation for F7 is:=$K$7:$K17.

But then when I use the formula for validation list in the the next column
(starting in cell G7), there will be too many nested if functions to be able
to have sub-category options for all 11 main categories. I have the
sub-categories in the same table as mentioned in the paragraph above to the
right of the main categories so that I can use th following function:

=IF(f7=$L$7,$L$7:$n$7,if(f7=$K$8,$L$8:$U$8€¦

"Naz" wrote:


Usually the best alternative is to use a Lookup function, however, without
more details its hard to give a fuller explanation.

_____________________
Naz
London


"mgdye" wrote:


I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

That works perfectly - thank you very much for your help Debra!

"Debra Dalgleish" wrote:

You can create dependent data validation lists. There are instructions he

http://www.contextures.com/xlDataVal02.html


mgdye wrote:
I have a drop-down listed validation for a main category of expenses in one
column, starting in cell F7. There are eleven categories: Automobile, Bills,
etc. Then in the folowing column, I want to drop-down validated list to be
dependant on the previous column's value to present sub-category choices. So
if I chose Automobile in Cell F7, I want the drop-down list in cell G7 to
display sub-categories related only to automobiles, such as Repairs, Gas,
etc. and not to show any other sub cateries for other main categories.

I have figured out how to do this by creating a table with the first column
being the main categories and then making A1's validation equal that cell.
So if the first cell in the table is K7, then I have Automobile in that cell,
Bills below it, etc and my validation for F7 is:=$K$7:$K17.

But then when I use the formula for validation list in the the next column
(starting in cell G7), there will be too many nested if functions to be able
to have sub-category options for all 11 main categories. I have the
sub-categories in the same table as mentioned in the paragraph above to the
right of the main categories so that I can use th following function:

=IF(f7=$L$7,$L$7:$n$7,if(f7=$K$8,$L$8:$U$8€¦

"Naz" wrote:


Usually the best alternative is to use a Lookup function, however, without
more details its hard to give a fuller explanation.

_____________________
Naz
London


"mgdye" wrote:


I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it worked.

mgdye wrote:
That works perfectly - thank you very much for your help Debra!

"Debra Dalgleish" wrote:


You can create dependent data validation lists. There are instructions he

http://www.contextures.com/xlDataVal02.html


mgdye wrote:

I have a drop-down listed validation for a main category of expenses in one
column, starting in cell F7. There are eleven categories: Automobile, Bills,
etc. Then in the folowing column, I want to drop-down validated list to be
dependant on the previous column's value to present sub-category choices. So
if I chose Automobile in Cell F7, I want the drop-down list in cell G7 to
display sub-categories related only to automobiles, such as Repairs, Gas,
etc. and not to show any other sub cateries for other main categories.

I have figured out how to do this by creating a table with the first column
being the main categories and then making A1's validation equal that cell.
So if the first cell in the table is K7, then I have Automobile in that cell,
Bills below it, etc and my validation for F7 is:=$K$7:$K17.

But then when I use the formula for validation list in the the next column
(starting in cell G7), there will be too many nested if functions to be able
to have sub-category options for all 11 main categories. I have the
sub-categories in the same table as mentioned in the paragraph above to the
right of the main categories so that I can use th following function:

=IF(f7=$L$7,$L$7:$n$7,if(f7=$K$8,$L$8:$U$8€¦

"Naz" wrote:



Usually the best alternative is to use a Lookup function, however, without
more details its hard to give a fuller explanation.

_____________________
Naz
London


"mgdye" wrote:



I am trying to create a validation for a column based off of the previous
columns value, which reqires many nested if functions, (10 to be exact).
However the limit of 7 nested if function prevents me from being able to do
this. With all the amazing things that excel can do, there has to be some
sort of work-around for this.

Any ideas?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html