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 here:

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 here:
>
> 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 here:
>>
>> 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