Data Validation using List (But needs unique list in drop down lis

Hi all,

In sheet 1, column A is my title name while column B is person name.
Sheet 1 is my database where i do data entry in this.

In sheet 2, contains my query page. In cell A5, i uses data validation - 
list, on this cell. Say in sheet 1 :

column A             column B
XXXXXXX            Mr A
YYYYYYYY            Mr A
ZZZZZZZZ            Mr A
AAAAAAA            Mr B
WWWWW           Mr C
DDDDDDD            Mr C

But In sheet 2, cell A5, I saw in the drop down list as follows:

Mr A
Mr A
Mr A
Mr B
Mr C
Mr C


But i want to see this in cell A5 instead (Unique name that is) :

Mr A
Mr B
Mr C

Thanks a lot.....
0
Utf
7/8/2005 1:50:02 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
361 Views

Similar Articles

[PageSpeed] 16

One play ..

In Sheet1, in B1 down is the names list

Mr A
Mr A
Mr A
Mr B
Mr C
Mr C
etc

Put in C1:
=IF(ISERROR(SMALL(D:D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROWS($A$1
:A1)),D:D)))

Put in D1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Select C1:D1, fill down to say, D100 to cover the max expected data in col B

Create a dynamic range "MyNames"
----------
Click Insert > Name > Define

Put in "Names in workbook:" box :  MyNames

Put in the "Refers to:" box:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$100<>"")))

In Sheet2
----------
Select A5
Click Data > Validation
Under "Allow:", select: List
Source: =MyNames
Click OK

The DV droplist in A5 will show only the unique list of names, viz. for the
sample data in Sheet1, it'll appear as:

Mr A
Mr B
Mr C

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Tan" <Tan @discussions.microsoft.com> wrote in message
news:1E5B348B-0460-404A-B795-18C039BDD5B1@microsoft.com...
> Hi all,
>
> In sheet 1, column A is my title name while column B is person name.
> Sheet 1 is my database where i do data entry in this.
>
> In sheet 2, contains my query page. In cell A5, i uses data validation -
> list, on this cell. Say in sheet 1 :
>
> column A             column B
> XXXXXXX            Mr A
> YYYYYYYY            Mr A
> ZZZZZZZZ            Mr A
> AAAAAAA            Mr B
> WWWWW           Mr C
> DDDDDDD            Mr C
>
> But In sheet 2, cell A5, I saw in the drop down list as follows:
>
> Mr A
> Mr A
> Mr A
> Mr B
> Mr C
> Mr C
>
>
> But i want to see this in cell A5 instead (Unique name that is) :
>
> Mr A
> Mr B
> Mr C
>
> Thanks a lot.....


0
demechanik (4694)
7/8/2005 2:32:38 PM
Reply:

Similar Artilces:

macro needed
hello listers to begin with i have no great excel skills what I am looking for is a macro that I can easily use to send mail to a list of users I have made a web page that students sign up on http://biosciences.utoledo.edu/prs enter there info when they press submit the data is saved to a text file that can be opened as a csv file in excel I would like to have a macro go row by row down the page and extract the email from column G and insert that in to an email and also the students name from coulmn B. The csv file is set up like this ID Fname Lname StudentID TransmitID Section e...

Using Publisher for long reports
Hi. I write long reports (50 pages) in which layout and style are very important. I'm thinking of switching from Word to Publisher since Word's management of pictures and layout drives me wild. Can anyone advise me if Publisher might be the right choice? Does anyone have some long document templates they could show me? -- Giles It sounds like Publisher would be perfect for you since you indication that the layout is important. I can't help with a sample tho because (1) the longest I've done is about 24 pages and (2) my newsletters contain personal contact information and...

How can I use my calendar and other features wo using Outlook emai
I have Windows7 OS. I upgraded to MSO 2007. I cannot access the calendar, etc. without setting up an email profile which my ISP will not allow using the email address. How can I use my calendar and other features without using Outlook email? I could use the calendar on previous versions without setting up an email account. Why can't you use Outlook w/o a mail profile? What happens when you try? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft....

Can I delete OE and just use Microsoft Outlook 2002
I would like to delete Outlook Express is this advisable? I am going to be using Microsoft Outlook 2002 which I recieved w/Toshiba PDA and already have it set up on the same PC as OE. Can I use Microsoft Outlook for multiple e- mail accounts? Differences Between Outlook and Outlook Express http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257824 With Outlook, you can integrate and manage e-mail from multiple e-mail accounts, personal and group calendars, contacts, and tasks. "Delia Marlowe" <anonymous@discussions.microsoft.com> wrote in message news:780901c4025...

Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell Examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Stephen Ford" <Stephen_Ford_no@spam_uwclub....

How do I open doc's from Publisher 2000 when I am using Pub 2003?
I recently purchased Publisher 2003 and can't seem to edit any document from a previous version. Are you running Norton AV? Try disabling NAV Script Blocking ~~~~~~~~~~~~~~~~~~~~ Start NAV Click Options. If a menu appears when you click Options, then click NortonAntiVirus. The Norton AntiVirus Options dialog box appears. Click Script Blocking. Uncheck Enable Script Blocking (recommended). Click OK. Restart the computer if prompted to do so. -- JoAnn Paules MVP Microsoft [Publisher] "GAR" <GAR@discussions.microsoft.com> wrote in message news:01222D01-7A00-4...

merging into Word with filtered data
Hello, I'm trying to do a merge in Word using data from Excel. However, I don't want to merge using data from the whole worksheet, but just filtered data (all people from London - place is one of the fields in the worksheet. I have no idea how I might do this. Any help gratefully received. I believe you can accomplish this from Word by using "Query Options" and selecting to merge only those records you're seeking. HTH PC "Italian Pete" <ItalianPete@discussions.microsoft.com> wrote in message news:3E62FCDC-42EC-48DB-92AC-857B224F085F@microsoft.c...

Data Loss in Publisher
Hi I run a W2k network in a school. I have Office Group Policies in place forcing the default save location for Publisher documents (Office XP version) to the pupil's home folders. All my PCs are identical (ghost images). Pupils have roaming profiles (some mandatory). Some users save work in Publisher and the next time they log on to the network and open the saved file, it's contents have gone! There is nothing exceptional about the affected users' user accounts and they have a mixture of mandatory and roaming profiles. Has anyone come across this problem before? Than...

DISTRIBUTION LIST #15
When I open one of my distribution list it comes up on a properties menu with the names listed but I can't make any changes. All the other list I have work fine. Any help would be appreciated. ...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Any way to clear out stock pricing data before or after going to Quicken?
With all the discussion a few months ago about taking control of our own data, I had gone through the steps to look at my Money file as an Access DB, and found that of my 80MB, more than half was daily stock prices. Thats not something I really want to convert and maintain. I can't imagine that its really needed, since transactions have their own price info. Is there any way to get rid of it in Money, in Quicken, or other? Thanks In Money Plus: Portfolio > Other Tasks > More > Clear historical quotes The process may be different in earlier versions. "JDelMar&q...

WinDbg: Unable to get verifier list
I've been attempting to get to the bottom of a recurring BSOD crash happening on my system. I've already had 4 crashes so far over the past two weeks. So I've identified that NTOSKRNL.EXE is involved in all of them so far. It always somewhere in the stack. So I enabled Driver Verifier on NTOSKRNL, as well as HAL.DLL, NTFS.SYS, and FLTMGR.SYS which were also identified on the stack during various of the events. Okay so I had my latest crash yesterday, and it occurred on NTOSKRNL as well. The Verifier was already enabled on the system prior to this crash, and then wh...

Referencing .NET 2.0 but end result uses .NET 3.5
I'm using DevStudio 2005 and I have a project that references System.dll ver 2.0.50727 and I accidentally compiled code that used an overload that only exists in version 3.5 because my intellisense showed it. I tried to compile it on another machine that never had the .NET 3.5 install and it wouldn't compile and the intellisense had that version of the overload yet both references looked identical; looking at ver 2.0 of System.dll The method in question was WaitHandle.WaitOne. ..NET 2.0 WaitOne() WaitOne(Int32, Boolean) WaitOne(TimeSpan, Boolean) ..NET 3.5 WaitOne...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

Using a drop down box to select all results
I have a summary page which pulls data together from several sheets an displays summary statistics etc. I want to be able to use a drop down list to select particular product and only show data relating to those products - so far so good. But ..... having put in my drop down and referenced it in my formulae now cannot show the summary data for ALL products. Is there a wildcard that can be used in drop downs to select all -- Message posted from http://www.ExcelForum.com You could add (All) to your list of products, and check for that selection in the formula. For example: =IF(A2=...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

using interop to create document word 2007
I am using c# and word interop to open a mailmerge document fill it in and then display it to the user. Once i display it the c# app has nothing more to do with the word document. The user can edit it as needed and then save it as a pdf and that is fine. The issue is I would like to set the default path for the document and it could be a network drive or sharepoint and will change every time also it will never be where the original document was opened. ...

drop down boxes #4
Hi everyone, Ok i'll outline the situation. I am trying to produce an invoice page on a system where the user can produce an invoice etc, and I am wanting to make a cell show the total cost for the accomodation. I have a drop down box for the location name and for the cost per day, and the number of days is shown in a separate cell. Is there a way to show the total cost of the accomodation in this single cell? Help needed urgently! Thanks, Andy. Andy I am not sure of what you want but from what you say, all you need is a simple formula in that cell. Say the charge per day is ...

Can I use Free Excel Viewer 97/2000 in Windows XP?
Microsoft downloads doesn't list the free excel viewer compatible with Windows XP. Is there a free download to view and print excel spreadsheets, that's compatible with Windows XP? Linda The Viewer for Office 97/2000 works fine under Windows XP. Gord Dibben Excel MVP On Fri, 22 Oct 2004 15:11:01 -0700, "Linda" <Linda@discussions.microsoft.com> wrote: >Microsoft downloads doesn't list the free excel viewer compatible with >Windows XP. Is there a free download to view and print excel spreadsheets, >that's compatible with Windows XP? Linda An...

summarise data
I have data on a sheet looking like this 5/5/2004 5/5/2004 7/5/2004 7/5/2004 7/5/2004 7/5/2004 8/5/2004 9/5/2004 9/5/2004 9/5/2004 and I want to summarise this into different cells so that it look like 5/5/2004 7/5/2004 8/5/2004 9/5/2004 i.e. I want to list individual occurances of repeating dates o numbers. Any help would be much appreciated -- Message posted from http://www.ExcelForum.com Sounds like a job or a Pivot Table perhaps? http://www.ozgrid.com/Excel/excel-pivot-tables.htm ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore...

creating a program that uses data from a sensor....
Hello, I want to create an MFC program that uses parameter values passed to it from a meter (specifically the resistance in ohms of resisters in an electronic circuit). What are the hardware components that would be needed? And what MFC class(es) would I use to accomplish this? Thanks, RABMissouri2006 On 21 Oct 2006 08:10:50 -0700, "RAB" <rabmissouri@yahoo.com> wrote: >Hello, > >I want to create an MFC program that uses parameter values passed to it >from a meter (specifically the resistance in ohms of resisters in an >electronic circuit). What are the ha...

Data archive with GP Manufacturing
Has anyone had experience with any data archive with GP Manufacturing. The product from Professional Advantage does not support Manufacturing archive, at least not in the documentation. Don't have an answer for you but we will be doing the same in the next week or so. Will report back when done. "DavidInterDyn" wrote: > Has anyone had experience with any data archive with GP Manufacturing. The > product from Professional Advantage does not support Manufacturing archive, > at least not in the documentation. ...

Transfering Outlook Data to a New PC
I've just built a new PC. Currently it has nothing on it other than Windows. When I install Outlook 2003 I'll need to transfer the data from another PC onto it, including all folders, emails and attachments in them and calendar data. What's the easiest and most effective way to do this? Find the PST file and copy it to the new machine and into the appropriate directory, what? Thanks. Copy the pst(s) to the new PC There isnt an appropriate Folder, there is a default folder created when Outlook is installed. http://www.howto-outlook.com/howto/backupandrestore.htm "Marts&q...

Drag and Drop Records
Hi, I need to create a relationship between two sets of data, Purchase Order and Production Schedule Order. I've reviewed many different ways to get this done but always come back to manual intevention due to the two sets of data being different. So, what I would like to do is create a form with two sub forms, both in datasheet view. The user drags a record from one datasheet to the other, which then updates the two fields in the table being dropped on. Just wondered, is this possible? and if so got any pointers on how to do it... Thanx in advance Marcus ...

Locking of entered data
I haev a form that currently allows you to enter new records and edit existing records. My key field is based on a number entered by the user from another system. Once the number is entered I do not want the user to be able to type over that number but still be aloud to add a new record. I tried the AllowAdditions and AllowEdits in the properties but if I set them to Yes AllowAdditions and No AllowEdits then nothing can be changed. I want them to be able to edit everything but the one field containing the number. Ideas? Glenna, The code I provided would go in the two places that I...