validation list

[Ruth]

Hi there

I have a worksheet that uses a validation list that has multiple entries of
the same thing, ei the validation list will have "Alpena" 4 or five times in
a row. I want each name just to show up once in the drop down box. Is that
possible, if so, how?

 

[Kassie]

Remove the duplicates?

--
HTH

Kassie

Replace xxx with hotmail

 

[Ruth]

I would-- but a have another validation list that works based on the
selection in this validation list. There are several different docks in each
port, so the first list is to select the port and the second on is to select
the dock that is located at the port selected. So if is delete the
duplicates I would ruin the next step in the data selection. Is there a way
without deleting the duplicates?

 

[Kassie]

You must set up your dependent validation lists correctly. Iow, have a list
of ports, and a list of docks for each port. Your list of docks then has a
list of unique ports.
To call the dependent list, you use a formula. Say you select the port in
A1, and the dock in B1. In B1's data validation you use a formula
=INDIRECT(A1)

Easiest way is to have a horizontal list of ports, each with a vertical list
of docks referring to that port. You name the horizontal list Ports, and the
vertical lists you name as the specific port it refers to.
So, your validation list is from say A1:G1, and this has a range name of
Ports.
Say in A2:A10 you have a list of docks referring to a port called Sydney,
then you name range A2:A10 Sydney.

--
HTH

Kassie

Replace xxx with hotmail

 

[Ruth]

I should have explained this in the beginning. I had the validation lists
and they were and are working fine. I decided that when new information is
added, ei a new port or dock, I am working in a different file in Access to
update all the information, then I have to do it again in this file in excel.
so I found out how to update the excel file from the Access by importing the
data-- however the way that it is done it lists duplicate ports-- and I
wanted a process to automate getting ride of the duplication so that I would
not have to do it manually-- or add information in 2 separate data bases.

So really what I would like to know is if there is a way to get rid of
duplication in a column -- that way I would only have to copy and paste in
the column that I use for validation.

Hope that made sense.

 

[Kassie]

The easiest would be to just overwrite the Excel database, rather than add to
it? In Access you only have one instance of each port/dock in the relevant
tables, so if you copy that and then select your validation range before
pasting, you will overwrite the old data with the new.

--
HTH

Kassie

Replace xxx with hotmail

 

[Ruth]

Hi Kassie

I must not be explaining it clearly. Thank-you for you help. But at this
point I am guessing that there is no automatic way to get rid of duplication
in a column in Excel.