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
347 Views

Similar Articles

[PageSpeed] 12

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:

account registure listing order
How do I change the listing in the account registure from the oldes transaction first to the newest transacton first -- Poor old fart Depending on version, click on the header "Date" in the register. Or, pull down the View pull down, select the Sort by pull out and go from there. "tomkatlee" <tomkatlee@discussions.microsoft.com> wrote in message news:E32E013C-2063-41E2-B365-75480E5DCA6E@microsoft.com... > How do I change the listing in the account registure from the oldes > transaction first to the newest transacton first ...

How to convert CString object to data type?
How can I convert CString from a EDIT control Dialog to data type for calculation? thanks, Take a look at _stscanf/_ttoi. --- Ajay Y Xu wrote: > How can I convert CString from a EDIT control Dialog to data type for > calculation? > > thanks, Hi Y Xu, Can you be more specific , what type do you want it to be converted to ? /Kiran Y Xu wrote: > How can I convert CString from a EDIT control Dialog to data type for > calculation? > > thanks, On 8 Jun 2006 07:31:57 -0700, kiran.inbng@gmail.com wrote: >Hi Y Xu, > >Can you be more specific , what t...

Membership List
Hi! I want to create a Report in Exchange Management Shell which should show me List and head Count of different Distribution Group members. With what cmdlet command and how can I do it? Thank you in advance ...

Help Need for Sales Comparison Report
I have a table of daily sales figures. Fields are: WE (Numberic for Week Number) Date (Date) CashSales (Currency) ChargeSales (Currency) Credits (Currency) What I need to be able to do is produce a report that shows a comparison of all sales by Week Number from 2007 vs current 2008 year, grouped by Week Numbers. Example: W/E 1 Date Cash Sales Charge Sales Credits Date Cash Sales Charge Sales Credit 1-1-07 $100.00 $50.00 $0 1-2-08 $110.00 $65.00 $0 1-2-07 $200.0...

List Validation #2
Hello Everybody, I have applied list validations to cell, say C4. The list contains two values : CY & CFS But when I copy & paste special values other than the list (sa WAREHOUSE from cell B4), excel accepts the same. Can anyone help me to restrict the cell updation only as per list. Thnks & Rgds, Rajendra -- Message posted from http://www.ExcelForum.com Hi with Data - Validation you can't prevent copy/paste operations. This would require VBA >-----Original Message----- >Hello Everybody, > >I have applied list validations to cell, say C4. > >The lis...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Concatenate mid, left then right data
Hi, I have searched other messages and my brain must be on holiday because I cannot get any of the answers I found to work for me. What I have is a column of data which has to now be changed for GL Reconciliation, looks like this- SMITH J_000063MVINS JOHNSTONE K_013565MVINS And I now need it to look like this- 000063,J SMITH,MVINS Most probably very simple but it still eludes me..... Any help would be much appreciated. Thank You Hi Try =MID(A1,FIND("_",A1)+1,6)&","& MID(A1,FIND("_",A1)-1,1)& LEFT(A1,(FIND("_",A1)-3))& ","...

Why do Data markers in Excel 2003 appear to be bold?
When crating charts in Excell 2003 the Data Markers (numerous points) appear to be bold and blurry. This does not occur in Excel XP. Marker size is set to 2. ...

list of valid e-mails
Hi, how can I get list of all active e-mails? Including aliases, public folder and printer addresses if exists and so on? (Exch 2003) Where do the mails for which address does not exist come and how to I forward them to any bin address? Thanks, Jan you can get the list of addresses using ldifde or csvde. Check the following on how to use these tools: http://www.exchangeinbox.com/articles/037/part1_ldifde_csvde.htm http://www.exchangeinbox.com/articles/038/part2_ldifde_csvde.htm You want to catch emails with invalid recipients right? Be aware that this will quickly ...

How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows from one column to the next? There's a function similar to this in Quark. Anybody know anything about this? Regards, If I understand the question correctly, and the information is being typed into the cells, you could try this: Select a block of cells, say A2:C10 Then, type a value. Press enter. If yuo press enter after each value entered, when you enter a value in A:10 and press enter, you're active cell will be B2. tj "M. Frazel" wrote: > Is there a way to link columns in Excel, so t...

Table DROP Misfires
Programming in Acc2000/XP. I use the following code snippet to create 4 local tables: sSQL = "SELECT * INTO " & sTableName sSQL = sSQL & " FROM tblIndex " < tblIndex is replaced with tblDetail, etc sSQL = sSQL & "WHERE FALSE;" DoCmd.RunSQL sSQL, False That is the basic code. There are 4 routines; each creating a different table (copy of linked table structure). The 4 routines have the ability to create 2 different sets of tables. I end up with 8 tables which are temporary tables in a split db. The program collects and proce...

Extender Fields in Smart lists
We are using AnyView to create customized Smartlists using extender fields. We can get the fields to appear but they can't be searched on intelligently... Any help would be appreciated. -- Tom This won't be a popular answer, but switch to SmartList Buider. LV "Flickwine" wrote: > We are using AnyView to create customized Smartlists using extender fields. > We can get the fields to appear but they can't be searched on > intelligently... Any help would be appreciated. > -- > Tom Haven't heard that one (anyview purchased by microsoft) althou...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Using match to return value
Is it possible to use match to return value without reference to other cells? =IF(MATCH(H219,{"00","01","02","03","04","05","06","07","08","09"},0),<<return corresponding value>> ) where corresponding value should return "00" = Positve - Negative "01" = Positve - Positive "02" = Positive - Neutral "03" = Negative - Positve "04" = Negative - Negative "05" = Negative - Neutral "06" = Neutral -Positve "07&quo...

using stationery fonts
I am a relatively new user of Outlook 2003. I chose a stationery to use for my outgoing messages, but when I type messages, the font associated with the stationery is not the default. I have checked the boxes to tell Outlook to use the font associated with the stationery, but it still defaults to Times New Roman instead of Tempus Sans. Any suggestions? ...

Using two Network Adaptors
Hi, I have a new Windows 2003 R2 Standard server running Exchange 2003 Standard. The server has two Broadcom 1Gbps network cards in it. I used the Broadcom software to Team these cards for Load balancing and Failover. Unfortunately, after running for a month or so I started getting errors where the Exchange server couldn't reach the DC's. I removed the Team and am using just one of the Network adaptors and everything is back to normal. It may be the other adaptor is faulty and I will check that out next weekend. What I would like to know is what is the best way to use this two ...

Using arrows (keys) to navigate in Excel
Not sure if I can do this but only way I can navigate between the boxes is if i press it with the mouse or if I use TAB and thats navigate it only to the next box (forward) if i press arrows Up,Down,Left or Right I am moving whole spreadsheet....so is there any way I can set it up so i actually navigate (moving withing the boxes) with my arrow keys. Thanks Sounds like you've pressed the Scroll Lock key. Any extra lights on the keyboard ? Regards Trevor <dmmatic@gmail.com> wrote in message news:1190826070.681706.256300@g4g2000hsf.googlegroups.com... > Not sure if I can do...

Data Charting Correctly
We are working with a table that has a Sell Price, List Price, and Profit Margin. When we try to chart it, it looks like it is adding the "Sell Price" and "List Price". The "Sell Price" which is in row 1, is plotted correctly. The "List Price" looks to be the total of the two. The "Profit Margin" is plotting with the "List Price". Is it in the way we have selected the table to chart? Does anyone have any suggestions? Choose the line chart option in the first column, not the stacked line chart option in the second. - Jon ...

How do I use the templates for job cost tracking?
I work for a design/built firm. We provide customers with a typed (Excel) job estimate. We then use this estimate in-house to maintain cost versus estimate. We need a template(?) so we can pull some of the information out for simple reporting purposes. Vicki, First open a new workbook, set the worksheet just the way you want,column width, row height, format cells, put the formulas in that you want. When it perfect, (before someone gives added input on what it should look like,and they will). "File", "Save As", Name the file someething that you can easily remember, ...

best use for x,z and zz report
now we are using z report for end of day and zz report for end of month (x report just to get hard copy of inday sales) is thier a better or more usefull way to use this three reports? best reqards Hi Bacha11....you describe what most RMS users (I believe) do with the X, Z and ZZ reports - if you have multiple shifts (example a morning shift and a afternoon shift) on your registers you could use the X and Z reports for shift reporting and the ZZ for end of day (it will include both Z reports from both shifts) - other than that I think you're good to go.... "BACHA11" wrote:...

Need to Reset a Site Folder in Exchange 2003 Server... GUIDGEN.EXE Q
I need to reset a site folder using GUIDGEN.EXE because of an offline address book problem that I've been having, when people sync in Outlook: 10:07:25 Microsoft Exchange offline address book 10:07:25 0X8004010F Anyhow, I'm following the instructions here: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q822444 And I'm a little worried about "When you create a new site folder, the data that was previously contained in the original site folder is lost." Will this blow away my public folders, and I'll lose the data? Does anyone have any experience they...

grouping Radio Buttons; Using ActiveX Controls
Hello experts! I’m trying to enter radio buttons on a sheet (not UserForm) and I’m using ActiveX Controls (I don’t want users moving the controls around on the sheet so I’m trying to make the objects locked). Now, I seem to be stuck as to how to get my 6 radio buttons into 3 groups, so that I can choose three buttons. Right now, it is only letting me choose one button! Basically, this is an example of what I want to do: http://www.techonthenet.com/excel/questions/grouped_rbs.php But I need to do it on a sheet and using ActiveX Controls. Thanks! Ryan--- -- Ryan--- I...

Some Public Folder mails not visible using IMAP
Windows 2000 SP4 server/Exchange 2003 SP Strange thing happened yesterday. In the the course of 2 minutes about 200 mails appeared in one of our Public Folders, mails which had been received earlier into one of the other Public folders. All the mails have correct sender/receiver/subject but have blank messages. The receiving dates have been changed to yesterday's date, but looking at the details in Outlook these mails were all first received on Jan 6, 2006. Even stranger is that when viewed using IMAP these mails are not visible, but using OWA or Outlook in Corporate mode, they are visib...

Use of WPSTOOL from WPSDDK pack
Hi to everyone, What is the use of "WPSTOOL" tool (Wireless Provisioning Services (WPS) tool) included in the WPSDDK pack delivered by Microsoft ? ("http://www.microsoft.com/whdc/connect/wireless/wps.mspx") if I undersood well the aim of it, (!!!!please say me if I'm wrong!!!!...) this tool permits to achieve an auto configuration of a secured Wireless (WiFi) network (via WEP/WPA keys) by giving an XML File as argument of the "wpstool.exe" program. And The XML file would contain all the caracteristics of the wanted configuration of the ne...

how can find out , which dll s used in a VC++ project
hi, i have create a VC++ application with shared dll option. When i try to run exe in of this project in another system. it gives errors that missing of some dlls how can we find which dll are used in our project. and where we can find those dll s? thank you. by, koti http://www.heaventools.com/ Rail -- Recording Engineer/Software Developer Rail Jon Rogut Software http://www.railjonrogut.com mailto:rail@railjonrogut.com "Koti" <koti@nannacomputers.com> wrote in message news:eQ0PJVaOFHA.2356@TK2MSFTNGP14.phx.gbl... > hi, > >...