2 criteria cells used to select which validation list to use

Greetings All:
(Excel 2003)
I'm trying to "clean up" various workbooks and consolidate into one workbook
(probably about 75 sheets, when done). I have come to the conclusion that,
for clarity of my formulas, I should have used range names.
I have the Name Manager, version 3.2 from Jan Karel, which hopefully will
prove to be more than a little useful.

My current problem relates to the use of validation, based on info
from two cells, (either/or), and requiring a result of 1 of 2 validation 
lists.
I hope the following is clear.

Cell C3:  Truck    Cell D3 = 0 (not applicable) or 1 (applicable)
Cell C4:  Trailer  Cell D4 = is opposite of above.
                    ie: if D3 = 1, then D4 = 0 and vice-versa

Cell C5:  Length    Cell D5:  Actual length, to be selected from 1 of 2 
possible lists

If D3 = 1, then the drop down list to have specific list of lengths
possible for a Truck, as listed in Range Name "TruckLengths"

If D4 = 1, then the drop down list is to reflect the list of lengths possible
for trailers, as listed in Range Name "TrailerLengths"

I need the references in D3 and D4, for many formulas, so I hope I
don't have to change my layout - although I will, if neccessary.
The correct validation list is to prevent the salesman from ordering,
for example, a 48 foot Truck, etc............

I hope there is a solution to my situation.
Thanks in advance for any response (preferably a helpful one)

0
BEEJAY (11)
12/21/2005 10:15:03 PM
excel 39879 articles. 2 followers. Follow

4 Replies
429 Views

Similar Articles

[PageSpeed] 4

Try this formula in the List Allow type in DV


=IF(D3=1,TruckLengths,IF(D4=1,TRailerLengths))


you might want another optione if neither are 1


-- 

HTH


RP
(remove nothere from the email address if mailing direct)


"BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
news:B8355B43-F503-42E7-B7BC-E73A593A1CC0@microsoft.com...
> Greetings All:
> (Excel 2003)
> I'm trying to "clean up" various workbooks and consolidate into one
workbook
> (probably about 75 sheets, when done). I have come to the conclusion that,
> for clarity of my formulas, I should have used range names.
> I have the Name Manager, version 3.2 from Jan Karel, which hopefully will
> prove to be more than a little useful.
>
> My current problem relates to the use of validation, based on info
> from two cells, (either/or), and requiring a result of 1 of 2 validation
> lists.
> I hope the following is clear.
>
> Cell C3:  Truck    Cell D3 = 0 (not applicable) or 1 (applicable)
> Cell C4:  Trailer  Cell D4 = is opposite of above.
>                     ie: if D3 = 1, then D4 = 0 and vice-versa
>
> Cell C5:  Length    Cell D5:  Actual length, to be selected from 1 of 2
> possible lists
>
> If D3 = 1, then the drop down list to have specific list of lengths
> possible for a Truck, as listed in Range Name "TruckLengths"
>
> If D4 = 1, then the drop down list is to reflect the list of lengths
possible
> for trailers, as listed in Range Name "TrailerLengths"
>
> I need the references in D3 and D4, for many formulas, so I hope I
> don't have to change my layout - although I will, if neccessary.
> The correct validation list is to prevent the salesman from ordering,
> for example, a 48 foot Truck, etc............
>
> I hope there is a solution to my situation.
> Thanks in advance for any response (preferably a helpful one)
>


0
bob.phillips1 (6510)
12/21/2005 10:38:29 PM
Works Great!!
Can you explain why the "false" portion of the IF statement
is not required, in this case?


"Bob Phillips" wrote:

> Try this formula in the List Allow type in DV
> 
> 
> =IF(D3=1,TruckLengths,IF(D4=1,TRailerLengths))
> 
> 
> you might want another optione if neither are 1
> 
> 
> -- 
> 
> HTH
> 
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
> news:B8355B43-F503-42E7-B7BC-E73A593A1CC0@microsoft.com...
> > Greetings All:
> > (Excel 2003)
> > I'm trying to "clean up" various workbooks and consolidate into one
> workbook
> > (probably about 75 sheets, when done). I have come to the conclusion that,
> > for clarity of my formulas, I should have used range names.
> > I have the Name Manager, version 3.2 from Jan Karel, which hopefully will
> > prove to be more than a little useful.
> >
> > My current problem relates to the use of validation, based on info
> > from two cells, (either/or), and requiring a result of 1 of 2 validation
> > lists.
> > I hope the following is clear.
> >
> > Cell C3:  Truck    Cell D3 = 0 (not applicable) or 1 (applicable)
> > Cell C4:  Trailer  Cell D4 = is opposite of above.
> >                     ie: if D3 = 1, then D4 = 0 and vice-versa
> >
> > Cell C5:  Length    Cell D5:  Actual length, to be selected from 1 of 2
> > possible lists
> >
> > If D3 = 1, then the drop down list to have specific list of lengths
> > possible for a Truck, as listed in Range Name "TruckLengths"
> >
> > If D4 = 1, then the drop down list is to reflect the list of lengths
> possible
> > for trailers, as listed in Range Name "TrailerLengths"
> >
> > I need the references in D3 and D4, for many formulas, so I hope I
> > don't have to change my layout - although I will, if neccessary.
> > The correct validation list is to prevent the salesman from ordering,
> > for example, a 48 foot Truck, etc............
> >
> > I hope there is a solution to my situation.
> > Thanks in advance for any response (preferably a helpful one)
> >
> 
> 
> 
0
BEEJAY (11)
12/23/2005 2:27:03 PM
That is because we are using the List type, and the final False would return
a False which just doesn't equate to list.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
news:FEBF8D76-179A-4C51-A4B9-EEB8F32EFBAC@microsoft.com...
> Works Great!!
> Can you explain why the "false" portion of the IF statement
> is not required, in this case?
>
>
> "Bob Phillips" wrote:
>
> > Try this formula in the List Allow type in DV
> >
> >
> > =IF(D3=1,TruckLengths,IF(D4=1,TRailerLengths))
> >
> >
> > you might want another optione if neither are 1
> >
> >
> > -- 
> >
> > HTH
> >
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
> > news:B8355B43-F503-42E7-B7BC-E73A593A1CC0@microsoft.com...
> > > Greetings All:
> > > (Excel 2003)
> > > I'm trying to "clean up" various workbooks and consolidate into one
> > workbook
> > > (probably about 75 sheets, when done). I have come to the conclusion
that,
> > > for clarity of my formulas, I should have used range names.
> > > I have the Name Manager, version 3.2 from Jan Karel, which hopefully
will
> > > prove to be more than a little useful.
> > >
> > > My current problem relates to the use of validation, based on info
> > > from two cells, (either/or), and requiring a result of 1 of 2
validation
> > > lists.
> > > I hope the following is clear.
> > >
> > > Cell C3:  Truck    Cell D3 = 0 (not applicable) or 1 (applicable)
> > > Cell C4:  Trailer  Cell D4 = is opposite of above.
> > >                     ie: if D3 = 1, then D4 = 0 and vice-versa
> > >
> > > Cell C5:  Length    Cell D5:  Actual length, to be selected from 1 of
2
> > > possible lists
> > >
> > > If D3 = 1, then the drop down list to have specific list of lengths
> > > possible for a Truck, as listed in Range Name "TruckLengths"
> > >
> > > If D4 = 1, then the drop down list is to reflect the list of lengths
> > possible
> > > for trailers, as listed in Range Name "TrailerLengths"
> > >
> > > I need the references in D3 and D4, for many formulas, so I hope I
> > > don't have to change my layout - although I will, if neccessary.
> > > The correct validation list is to prevent the salesman from ordering,
> > > for example, a 48 foot Truck, etc............
> > >
> > > I hope there is a solution to my situation.
> > > Thanks in advance for any response (preferably a helpful one)
> > >
> >
> >
> >


0
bob.phillips1 (6510)
12/23/2005 10:44:56 PM
Thanks for the response.
I'm sure I'll run into other examples which will help
reinforce this idea.

"When is an if statement not a (normal) if statement"?
  "When it is a list statement"
Cool!!
Kinda like: When is a door not a door?  When it is ajar.
"Things are often not as they first appear"
Thanks again. Till next time.

"Bob Phillips" wrote:

> That is because we are using the List type, and the final False would return
> a False which just doesn't equate to list.
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
> news:FEBF8D76-179A-4C51-A4B9-EEB8F32EFBAC@microsoft.com...
> > Works Great!!
> > Can you explain why the "false" portion of the IF statement
> > is not required, in this case?
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Try this formula in the List Allow type in DV
> > >
> > >
> > > =IF(D3=1,TruckLengths,IF(D4=1,TRailerLengths))
> > >
> > >
> > > you might want another optione if neither are 1
> > >
> > >
> > > -- 
> > >
> > > HTH
> > >
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
> > > news:B8355B43-F503-42E7-B7BC-E73A593A1CC0@microsoft.com...
> > > > Greetings All:
> > > > (Excel 2003)
> > > > I'm trying to "clean up" various workbooks and consolidate into one
> > > workbook
> > > > (probably about 75 sheets, when done). I have come to the conclusion
> that,
> > > > for clarity of my formulas, I should have used range names.
> > > > I have the Name Manager, version 3.2 from Jan Karel, which hopefully
> will
> > > > prove to be more than a little useful.
> > > >
> > > > My current problem relates to the use of validation, based on info
> > > > from two cells, (either/or), and requiring a result of 1 of 2
> validation
> > > > lists.
> > > > I hope the following is clear.
> > > >
> > > > Cell C3:  Truck    Cell D3 = 0 (not applicable) or 1 (applicable)
> > > > Cell C4:  Trailer  Cell D4 = is opposite of above.
> > > >                     ie: if D3 = 1, then D4 = 0 and vice-versa
> > > >
> > > > Cell C5:  Length    Cell D5:  Actual length, to be selected from 1 of
> 2
> > > > possible lists
> > > >
> > > > If D3 = 1, then the drop down list to have specific list of lengths
> > > > possible for a Truck, as listed in Range Name "TruckLengths"
> > > >
> > > > If D4 = 1, then the drop down list is to reflect the list of lengths
> > > possible
> > > > for trailers, as listed in Range Name "TrailerLengths"
> > > >
> > > > I need the references in D3 and D4, for many formulas, so I hope I
> > > > don't have to change my layout - although I will, if neccessary.
> > > > The correct validation list is to prevent the salesman from ordering,
> > > > for example, a 48 foot Truck, etc............
> > > >
> > > > I hope there is a solution to my situation.
> > > > Thanks in advance for any response (preferably a helpful one)
> > > >
> > >
> > >
> > >
> 
> 
> 
0
BEEJAY (11)
1/2/2006 3:43:02 PM
Reply:

Similar Artilces:

formatting dates by year #2
I would like to know if there is a way to format dates so that they are formatted by year only. For example, if I click on Format -> Cells, there are a bunch of options, but none of them list to show the year only, just mm/dd/yy or mm/yy, etc. I am trying to create a chart so that only the year shows up. Also, I have tried doing Custom->and "yyyy" in type, but only 1 specific year shows up (i.e. 1905) for all the values. I would like to keep my values. Thanks much! ...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

'top' is not a valid value for the 'vertical-align' property.
Well, it is, actually, and the resulting .chm file works properly in HTML Help. But it's a bit tedious getting that wrong useless error message from VS2002 on every build. (Well, not every build, it only does it sometimes actually.) Any idea how I can persuade it to stop telling me this? -- Tim Ward Brett Ward Limited - www.brettward.co.uk ...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Increment A2 from A1 and A2 Sum #2
I have two cells: A1 - manual value B1 - automatic increment = B1 + A1 How can i make it? (=error: circular reference) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com ...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

anyone else having problems with the 12.1.9 "autoupdate"? #2
Hi all... The 11.5.5 autoupdate worked fine. The 12.1.9 autoupdate downloads (apparently), but then it goes right back to showing that the 12.1.9 update is available -- the updater itself never actually launches. Busy servers, maybe -- and the download isn't ever actually completing? Or bad updater on the update server for 12.1.9? The manual download from Mactopia of 12.1.9 installed properly -- this is just the auto-update that's not working now. - Steve You're right, the auto-update fail. It should be pulled or a message sent instructing to update by downloading directly...

Contact oddity and why are there 2 Contact Lists?
Running Win 7, and was receiving emails in WLM with my maiden name listed in the TO:. I found out that I had my maiden name listed in Contacts (I run a high school mailing list here) so I decided to check it out and changed the last name to all caps. Sure enuf the next email I rec'd showed that name. I then deleted the email address connected to my name in the contact list and everything is back to "normal" (whatever that is <G>) Why in the world are there 2 contact lists on my computer......one thru WLM and one thru Windows Contacts? I'm confused. ...

GPS 8 service pack 2 and add new company
After installing service pack 2 for GP 8, I am not able to add or log on to the new company. Error during upgrade is “Entries haven't made to all required fields. Would you like to show the required fields on all windows in greatplains” When I try to log on to GP getting another error “file for this company have not been updated” Please help Rajesh ...