how can I exceed the nested if fuction limit #2

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?
0
mgdye (4)
1/30/2005 12:19:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
865 Views

Similar Articles

[PageSpeed] 30

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?
0
Naz (61)
1/30/2005 12:23:01 AM
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?
0
mgdye (4)
1/30/2005 12:53:02 AM
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

0
dsd1 (5911)
1/30/2005 1:05:17 AM
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
> 
> 
0
mgdye (4)
1/30/2005 3:01:07 AM
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

0
dsd1 (5911)
1/30/2005 2:09:17 PM
Reply:

Similar Artilces:

Automation #2
I have created five (5) grids within Excel consisting of correlations comparing various commodities. Each correlation grid represents years of data (i.e., 5yr, 10yr, 15yr, etc.). Right now, I am manually checking each commodity across every grid to see which other commodities are closely or loosely correlated. At the same time, I check to see if these correlations are consistent across all years of data. Once the correlation is manually validated, I type the summary list into yet another worksheet. Basically, here's an abbreviated example of what the grids look like: AD BO BP C CC CD...

Add vertical lines to a line chart (already has 2 Y axis)
Hi, I have a report I update each month. And while most of the information is based on numbers one of the series is a percentage, so has it own axis. I've set up the spreadsheet so that each week when data is added then another part of the spreadsheet shows it, this part of the spreadsheet only shows the last 28 weeks, so I have no need to change the cells the graph is looking at (since it always shows the last 28 weeks) The problem is that I need to have vertical lines to show different months. The closest i've managed is to put a seconardy X axis with gridlines for every mont...

Re: server and client threads #2
On 2/3/2005 1:48 PM, RSN_@_COMCAST.NET wrote to ALL: -> When you spawn the child thread you ordinarily pass it a parameter. -> What I do is make that parameter a pointer to the instance of the -> server class that spawned the child. That way, the child can easily -> notify its parent about any special events that may be useful. -> Termination is one such case! I also find it very useful to send a -> stream of diagnostic and debugging information to the parent who can -> display it, log it, or ignore it depending on circumstances. Good point. Now since my server thread...

How many entries can Excel handle!?
I have close to 1000 entries in my Excel spreadsheet, and am trying to figure out why entries are coming up missing. I often do custom sorts. This may be when entries get tossed out? You are no where near the limit. Check out the help file article "Excel limits and specifications". There must be something else going on. -- Best Regards, Luke M "Rebecca Sage" <RebeccaSage@discussions.microsoft.com> wrote in message news:4241294E-484E-4A8A-9304-0E0C45E643A3@microsoft.com... >I have close to 1000 entries in my Excel spreadsheet, and am trying to ...

How can i to put a SharePoint web server in my DMZ zone.
I want to expose just one of my SharePoint web servers into my DMZ zone, how can i do this? ------=_NextPart_0001_B573D3EC Content-Type: text/plain Content-Transfer-Encoding: 7bit -------------------- Thread-Topic: How can i to put a SharePoint web server in my DMZ zone. thread-index: AcqsG05Zq7kgx6kqQe6fWBwYUxg65Q== X-WBNR-Posting-Host: 63.147.159.188 From: JACR <JACR@discussions.microsoft.com> Subject: How can i to put a SharePoint web server in my DMZ zone. Date: Fri, 12 Feb 2010 11:41:01 -0800 Lines: 2 Message-ID: <ACE6C606-D323-42E2-880B-A4815E253E87@microsoft....

Outlook 2003
If someone sends me several pictures they dont automatically display one above the other like they used to in OE before i upgraded to Outlook 2003? I can find the bit about automatically downloading pictures but those are for HTML emails - i want them to display if they are sent as attachments! driving me mad and wasting lots of clicks and time! help please!! thanks Not possible. And note that you didn't upgrade but are using a completely different program now ;-) -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD...

Resolution to CRM 1.2 database export failed error during 1.2 to 3.0 upgrade
After struggling with this error working with MS Support and receiving no resolution, we were able to solve the problem through KB article 916924 (see below). Why MS Support did not point us to this in the first place is quite frustrating, so hopefully this will help others! CAUSE Cause 1 This problem occurs because there are saved queries in the Microsoft CRM 1.2 databases that are malformed. This problem usually occurs because a third-party product is not completely removed from the Microsoft CRM databases before you upgrade to Microsoft Dynamics CRM 3.0. Cause 2 This problem occurs if th...

Sum From Beginning of Column #2
How do I create a formula that adds from the top of a column? Here's the situation. I have a column of numbers (with a header of "Pay"). To the right of that colum I want to keep a running total ("Yearly Total"). Every time I get paid, I'm going to enter the amount in the pay column and then I want the Yearly Total column to automatically add it. When I create a fomula (say =SUM(B2:B21)), and propigate it down, it doesn't start at the top. In others words the next formula is =SUM(B3:B22). I want it to be =SUM(B2:B22). And, if a value is not filled in on t...

CFormView #2
Hello, I am writing a wizard and I want to use the CFormView in a dialog for each window, except I can't figure out how to do that. Can someone give me some pointers? Thanks ...

two colum lookup #2
i would like to see the cells in colum A & B which coiside with the cells in colums F & G i will ammend the original so that i can be understood better and send shortly -- alexanderd ------------------------------------------------------------------------ alexanderd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4984 View this thread: http://www.excelforum.com/showthread.php?threadid=278114 ...

Downgrade RMA 2.0 database to 1.3?
Is there anyway to do this? NO. Can go up but not back unless you have backup from before the upgrade ...

Change event missing from Forms 2.0 combo box.
I'm doing a project in Access 2000 using forms and MS Forms 2.0 combo boxes. I'm not using the native Access combo box because it lacks an Add Item method and is therefor very difficult to populate. I want to do something when the user makes a selection in the combo box so I need to respond to the Change event. The object browser shows a number of events including Click and Change available for the combo box. However, these events do not appear in property list of the combo box so I cannot get to them. I have confirmed that I'm using the Forms 2.0 combo box and not the native o...

SQL setup RMS 2.0
I am setting up a new system with 3 registers (Win XP SP2), two back office computers (Win XP SP2 and SQL 2005 Express), and Windows SBS 2003 with SQL Server 2005 Professional. The problem: Can't get the back office workstations to connect to the SQL Server. I installed RMS on the server, it connects to SQL and run perfectly. When I try from the workstation using RMS Admin, I get a time out error. I did everything in KB932078 without success. Must be something very minor with major consequences that I've missed. Can anyone help? Thanks Rich -firewall -sql surface area co...

Publisher cannot complete the operation #2
I have Publisher 2000 installed on my computer and recently I downloaded version 2007 to try. Now when I open up Publisher 2000 and try to open templates for the project/s that I am interested in doing the template that I pick downloads half way and then a message pops up telling me that "Publisher cannot complete the operation". Can someone please tell me what I should do to fix the problem? From where are you trying to download the template? Is it a program template or do you go online? Is 2007 still on your computer? Can you accept the ActiveX control? Downloading clips, ...

how ,we can remove flickering of form control
hi I have some progress bar control on my form.I have set different color for different progress bar. BUT ,PROBLEM IS THAT WHEN PROGRESS BAR SHOWS PROGRESS ,SOME FLICKERING ALSO APPEARS. HOW ,WE CAN SOVE IT. Arbind Arbind, You seem to have big problems with flickering forms :-))) As you have both - if I remember correctly - a flickering grid and now a flickering progress bar, I would suspect that the problem is that you are triggering unnecessary updates yourself - you might want to check your code for invalidations of the form, where invalidations of a control is mo...

Mac Word 2008: How can I tile a background or watermark?
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi, <br><br>My objective is: <br><br>1) tile an image to fill the background of a document <br> 2) superimpose a filled text box <br><br>to create a white text block, with an image-tiled border running around it. <br><br>I've tried doing it as a background &quot;texture&quot; and as a watermark. The background texture, although it looks fine on the screen, disappears in Print Preview and will not print (I do have the &quot;print backgrounds&quot...

Can i tell the cell to equal the pattern in another cell?
Is there any way i can tell a cell to equal the background colour of another cell? For example on one spreadsheet the cell is pink and i want this colour to map to another cell in a different worksheet. Many thanks. I forgot to mention the information in the cell is different, it's just the colour i want to replicate. "Snowy" wrote: > Is there any way i can tell a cell to equal the background colour of another > cell? For example on one spreadsheet the cell is pink and i want this colour > to map to another cell in a different worksheet. > > Many thanks. ...

I can't select cells on Excel using the touch pad, and neither the sheet not the workbook is protected
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) SOS folks, <br><br>I am in a pickle! So, as I was working on Excel, something happened because now it doesn't let me select cells with the touchpad, but only using the arrow keys. It's very strange. I went to tools to make sure that neither the sheet nor the book is protected...What can it possibly be? <br> I would be fantastic if somebody could cast some light on this technical glitch. <br><br>All the best, <br><br>Joseph Hail mates, I solved the problem by updating the software...

Emails not showing up in Outlook but can be seen on Webmail
I've got several clients saying that in Outlook 2003 and Express its saying they have new email but none of these new emails are in their inboxes. so i told them to log into webmail, and they can see the emails there... all of these clients are using IMAP and could have very large inboxes.. what could be the problem here? why isnt outlook downloading the emails properly? Do they have LAN or WAN access? -- Jonathan No Warrenties Implied, Did you do a FULL backup today?????? "beanfieldtech@gmail.com" wrote: > I've got several clients saying that in Outlook 2003 and E...

check register template #2
at the top of the template a range of cells says stationary and if you look at it in print preview it stays on top of all other applications......does anyone know how this works???? how about you JulieD Hi Michele you mean how does row 4 & 5 get repeated on each page that you print out? if so, file / page setup - sheet tab, rows to repeat at top. Cheers JulieD "Michele" <Michele@discussions.microsoft.com> wrote in message news:BAB47D9F-6674-4395-B629-CCCBD067E89D@microsoft.com... > at the top of the template a range of cells says stationary and if you > loo...

how can i print cards on a single page and edit side two and make.
This isn't. Post your message in this bit, describe it in the top bit. That way, you see the entire message rather than just the start. -- Ed Bennett - MVP Microsoft Publisher ...

Can't attach a stored procedure to a Crystal report??
"Information is needed before this report can be processed" I get this error when I try to run a report through CRM that has been built with a stored procedure as the data source. Preview mode in Cystal shows the data just fine. Seems like this might be a bug. Any ideas? Thanks, Dave ...

Help! Workspace creation failed and PS2007 still thinks it exists although I can't see it in PWA
I had a workstation creation fail on an "XYZ" project. When I try to re-publish using a different template (which I know works), it says the workspace already exists. Problem is I can't see it in the Project Workspaces area of PWA, nor from the Sites area of PWA, nor from the Project Workspaces area of the Server Settings page, nor from the Sites and Workspaces are of the Site Settings page (of PWA). I've got to figure out what about the newly provisioned template it doesn't like (I've since then changed to the previous version), but first I need to get r...

How to limit the number of instances of an application on a server?
Hello all, I want to know (and to limit) the number of instances of my application on a server such as Microsoft Server 2003 or 2008. I know how to look for multiple instances of an app using Joe's code ("How to avoid multiple instances.. http://www.flounder.com/mvp_tips.htm#Avoiding%20Multiple%20Application%20Instances). I've implemented Joe's code and tested it the following way : Using Vista, I log in as user A and start my application. Then, without closing user A session, I log in as user B and start the same application. I had hoped that the mutex created when running th...

Can this be done ??
Sorry to post again, but 24 hours on with no response to my initial post, I think it unlikely to now be read.........may I ask again with a little more explaination.... any help appreciated. I have 7 foxpro database files that each contains specific information about clients. One database (personal) contains the client personal detail whilst the other files are referenced by the unique client_ID and contain clinical information about the 'clients'. I would like to create a worksheet that lists all the client name / address ( from the 'Personal' file ) and certain fields from ...