Validation list based on criteria

Hi

I want to create a data validation list based on certain conditions. 

Let me try to explain by example.

A1 = oranges		B1 = oranges
A2 = apples		B2 = no record
A3 = pears		B3 = pears
A4 = pears		B4 = pears
A5 = grapes		B5 = grapes
A6 = apples		B6 = no record

B:B value is based on a relative formula determined by the value in A 

A:A can have duplicate values

I want to create a validation list based on B:B where the formula result is 
“no record” The list needs to be populated with the relative value in A

For example:

Since B2 and B6 = “no record” then the validation list should contain the 
value “apples”

Where there are duplicates values in the validation list these are to be 
filtered out.

Thank you for your interest

0
Utf
11/30/2009 10:22:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

0 Replies
804 Views

Similar Articles

[PageSpeed] 20

Reply:

Similar Artilces:

Refreshing Lists...
Greetings :) I wanted to know if anyone could tell me if there was any way to either change the list-refreshing interval, and/or disable it and force a manual list refresh. My reason for doing so is that the lagtime involved on a normal basis on my HQ Manager is becoming more and more troublesome as it tries to refresh lists multiple times every minute. Sometimes I experience hangtimes of over a minute in the middle of running a process while someone's on the line waiting. Any help would be GREATLY appreciated! This is a multi-part message in MIME format. ------=_NextPart_000_0...

Smart List #2
Trying to create a smart list accounts payable aging report and download to excel, insteat of using the report, purchasing, trial balance, etc. Need to download to excel and the "regular" report is an ugly csv file with wrapped lines. The smart list is so much easier with excel. A/R is easy in smart list, but how do you create the A/P aging? I don't see aging categories or the appropriate columns to allow you to create it. Any ideas? Thanks You could export the transaction details and exclude those transactions that have a current amount equal to 0. Then, in Excel, ...

fix cell contents before deleting column from which formula's are based
I want to e-mail a spreadsheet to someone showing only the calculated results and deleting the column from which the formula's are based. I have done this (fixing the cell contents) before but can't remember how and am obviously not wording my query correctly for the help function. Thanks. Erica Copy, paste special, values? then delete the column? >-----Original Message----- >I want to e-mail a spreadsheet to someone showing only the >calculated results and deleting the column from which the >formula's are based. I have done this (fixing the cell >cont...

Displaying data based on Drop Down List Selection
Hello, hope someone can help. I have a list of records in Excel which are attributed to teams in my organisation. Say its a list of staff. What I need is to be able to send the file out so that a user can select from a drop down menu the team name and the list displays all the staff in that team. I know that they could just use a filter for this themselves but these these users are not even comfortable doing that!! I need to make is simple so wanted a big obvious drop down list with the teams in, they select it and see all the relevant records. Has anybody got any ideas? Would be ver...

Select criteria based on the field name the user selects
Hi all, I am facing with aproblem where in i have a small access db with forms and reports now i want to change or set the where condition of a query based on the value the user selects from the form through a combo box. For ex:- if user is selecting Invoice_date then the where condition should search based on invoice date if the user selects any other field the querys where condition should search based on that particular field.Can any one help me in getting a solution for this. I have a sample database that shows how you can use various controls to set the filter of a query that i...

Query Based Distribution Groups-Issues?
I am looking at implementing Query based groups and am curious, what are some of the problems I am might run into? Example: Outlook 2003 cached mode? OWA? Active Sync? and most of all: If I am going to replace an existing group, I am planning on, after hours, deleting the existing group and creating a query distribution group. How will this affect the users and their offline address books. Thanks, Bob As far as I can tell you can't assign permissions for example to Public Folders to Query Bases Groups - that was a big one for me. Mark "Bob Williamson" <BobWilliamso...

Cannot sum in report footer based on group footer
I have a calculated control on group footer called txtFlex =IIf([semester]="Fall Full-Time",[FT_Fall],IIf([semester]="Spring Full-Time",[FT_Spring],Sum([Hour]/2))) I want to total it in report footer =sum([txtFlex]) When I run the report, it asks parameter 'txtFlex' in report footer. I double checked the spelling and it's correct. I even tried to use Expression Builder to use mouse to pick the control but it still gives me the error message. If I delete the control in report footer, no error message. But I do need that summary control in report footer. W...

how to delete "Lists" tab from the left pane of project web access
I have created a document library in PWA page . In the left pane there is new document library under the Lists title with the name of EPM Support. but after 5 minutes i have deleted this one . but Lists title is still there. how can i delete this "Lists" . Kindly reply me soon. ...

Excel filter list navigation
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi! <br><br>Can anyone please let me know if it is possible and/or how to navigate in a Excel list using the Autofilter drop down &quot;list&quot; without always going back to beginning of but, instead, going from the selected item (as in the PC version)? <br> I have lists of thousand items to check one by one and always going back to the beginning is a pain. <br><br>Thanks In article <59bb1352.-1@webcrossing.JaKIaxP2ac0>, RALEX@officeformac.com wrote: > Ve...

Multiple criteria IIF function help
Hello, I am trying to create an IIF query field where if RtgGrp not equal NA or Non, the return Yes, otherwise return No. But it is not working. What is wrong with the formula? Thanks NoNONV?: IIf([RtgGrp]<>"NA" or "Non","Yes","No") On Mon, 17 May 2010 09:18:01 -0700, Cam wrote: > Hello, > > I am trying to create an IIF query field where if RtgGrp not equal NA or > Non, the return Yes, otherwise return No. But it is not working. What is > wrong with the formula? Thanks > > NoNONV?: IIf([RtgGrp]<>&...

Validation Boxes and dependant cells
Here is the problem... I have a list on sheet 2: THis is not the complete list, the actual i 400 names long. the range is called "Names" A B Derek | 575 Bob | 500 Jonny| 200 Doug | 300 On sheet 1 i have a validation box in row A that applies to the rang of "names" in sheet two I would like to have the number that coresponds with the name come u in cell B when the validation box is filled in A. Thanks, Dere -- Message posted from http://www.ExcelForum.com Hi use can use VLOOKUP for this in cell B on sheet 1 type =VLOOKUP(A1,Names,2,0) where A1 ...

Distribution list not sending msg(s)
Hey everyone, We have a Windows 2003 domain and are running Exchange 2003 /sp1. I created a email enabled distribution list and added two people to it for a test. I selected it from the GAL and sent a test msg. but it never arrived. I went to the "Message Tracking Center" and found that it had been "Submitted to Categorizer" but never delivered local. Anyone have any ideas what might me wrong? TIA, Clayton do you have an expansion server specified on the "advanced" tab? any restrictions on the list? -- Susan Conkey [MVP] "Clayton Sutton&...

Outlook 2000 task list seems to be linked with my inbox
when I accept a task, it is added to my task list as it should be, but it also stays in my inbox. if I delete it from my inbox, it is also deleted from by task list. how can I fix this? ...

Data Validation Question #3
I have a survey where users score things on a linguistic scale (e.g. Very Satisfied, Satisfied, etc.) and I want to convert that to a numeric score, but still keep the Linguistic Value. I was hoping there was a way to have a cell with the linguistic value and the a cell with the numeric score, but have them both show up in a dv cell. -- cmk18 ------------------------------------------------------------------------ cmk18's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6047 View this thread: http://www.excelforum.com/showthread.php?threadid=474120 I don'...

Sorting large Lists
I have been trying to sort large lists of data using various formulas but to no avail. The problem I have is my worksheets consist of approx 50 columns for products and 365 rows for the days of the year for each client. Because ordering patterns are not the same, I want to see when the last time a client had ordered: Example 1: Product no1, product no2 with product no18 Example 2: Product no3, product no10 with product no20 The problem I have encountered is that what ever formula I try, it always returns the last day the product was ordered not when it was last order with say product 1...

SP1 List of Fixes
Where is this document! The official link is useless http://support.microsoft.com/common/canned.aspx?H=Microsoft%20Exchange%202003%20SP1&Query=kbExchange2003sp1fix&CDID=EN-US-KB&LCID=1033&product=exch2003 Are we all mad applying this SP without knowing what it does? You can find all of the wonderful info your looking for here :D http://www.microsoft.com/technet/prodtechnol/exchange/2003/library/sp1rn.mspx -- Thanks Dave [MCP, MCSE} me@davidchadwick.com http://www.davidchadwick.com "Any intelligent fool can make things bigger, more complex, and more violent. It tak...

Sort address list by using LDAP query
I am not sure if it is possible on Exchange 2003. Currently our address book is sorted alphabetically by its display name entries. I want to sort it so all distribution lists will become on top of the list and followed by user names. On Exchange System Manager, under 'All Address Lists', I have an Address List with the following LDAP query (using 'Custom Search'): (&(&(mailnickname=*)(|(&(objectCategory=user)(company=*))(&(objectCategory=group)(displayName=*))))) Is there any LDAP keyword that I can add here so it can be sorted as I want? Thank you in adv...

Price List + Alias
Dear all, I have an upcoming catalogue/sale coming up and what would be incredible useful to have would be a report, such as the price list report, with the addtion of the barcodes. This would allow us to see what item lookup codes have a barcode (so would then need to be flagged to have them added as they come from the warehouse) and which stock could be put straight out. We did try the alias report but if there is no barcode for the product of course it will not appear on the report. We use sub description 1 as a filter to populate the report, example - LB208. Any help would be gr...

Query Based Distribution List Note
We are running Exchange 2003 and using Query Based Distribution Lists (QBDL), viewable through the GAL. Our users are complaining (and building their own lists) because they can't see who is included in the list. We use desciptive names, but they need more information. All QBDLs are based on a company, a department or part of a title. I have asked our administrator to place in the "note" field of the QBDL what the parameter(s) were. For example, "people with COMPANY beginning with ABC". How do I tell my administrator(?) to edit the note field? Don In ADSIEdit, ...

Deleting Distribution List
When I delete a Distribution List, is each Contact in the list deleted from the Address Book? -- Ed Morgan Since there is no information ever stored in the Address Book view, your question is confusing. Nor do we know if the members of the DL in question have underlying records in your Contacts Folder or just exist within the DL. If you created the DL using Contact Records that exist in your Contact Folder, the Contact Record will not be deleted when you delete the DL. -- Russ Valentine "Ed Morgan" <EdMorgan@discussions.microsoft.com> wrote in message news:...

bulleted list of parallel items
I am writing a memo that has to contain a bulleted list of parallel items I have 8 items and want to list 4x4 side by side with bullets Use two columns (Format | Columns). For more, see http://word.mvps.org/FAQs/Formatting/UsingColumns.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "peggyl" <peggyl@discussions.microsoft.com> wrote in message news:28C244B9-928B-4447-B5C5-148BFFCB347F@microsoft.com... >I am writing a memo that has to contain a bulleted list of parallel items > I have 8 items ...

File/folder drag/drop on List control
Hi, I am using a List control in an MFC application. How do I enable drag & drop of only files & folder on the List control (set to Report mode)? Regards, Steve Here is some code that might help you get started, but basically, you have an OnDropFiles() function, call DragAcceptFiles() and catch the WM_DROPFILES message: http://www.codeguru.com/cpp/controls/listview/dragdrop/article.php/c941/ // In message map ON_WM_DROPFILES() // In OnInitDialog() DragAcceptFiles(); // Handler function void CAddDataView::OnDropFiles(HDROP hDropInfo) { CDialog::OnDropFiles(hDropInfo); ...

Moving from static distrbuation lists to dynamic
We will be moving from static lists to dynamic lists in the coming months. INFO: I have about 500 exchange enabled boxes. I have one domain with a WAN It is for a school of from ages 1 - 12 with 13 schools and 1 district office. Currently little info is posted in active directory to tell all users about. We have OU for admin, teacher, student in each school. I still do not have all the info gathered about what static lists are out their. I just took the job. But we have an alldomain list, allschoolteacher lists, and many others. I see that you can use the custom atribuate in AD to base ...

PDC down
Our primary domain controller bit the dust yesterday, but backup controllers took over as expected, with one exception. When using Outlook in our Exchange 2003 environment, the Global Address List is now being pulled from an old Exchange 5.5 installation, in a satellite office over the WAN, even though it's no longer an active member of the Exchange environment, (the computer is still online for other reasons, primarily as a DHCP server for the remote office where it's located), and our Exchange 2003 is still up and running fine in all other aspects. This is obviously causing problems ...

Direct email adding items to activities list
When using direct email some emails are not sent to the contact but simply added to the activities list then I have to send them from there. This only occurs sometimes. it occrs when sending both individual mail and bulk mail. When sending to bulk mail, only some emails are added to the activities list and the rest send normally. any ideas?? ...