Excel drop down list problem

  • Thread starter Thread starter junx13
  • Start date Start date
J

junx13

Hi,
I would like to implement a drop down selection list that take
the list items from a column in another worksheet in the sam
workbook.

What I have done is insert->name->define

a range which is the following:

=OFFSET('LO - Local Region'!$D$19,0,0,COUNTA('LO - Loca
Region'!$D:$D),1)

In the columns in which I want the list, I have use data validation fo
the whole column so that I have a drop down list of items to choos
from to input.


This works, but unfortunately, it does not provide me with UNIQU
items. that is, when I look at the drop down lists, there are as man
duplicates as there are in my range. I would like to make each ite
only appear once.

Any ideas
 
Make sure that there are available 2 rows before row 19 on sheet LO - Local
Region. And D18 houses a pertinent label while columns E and F are available
for the required calculations.

In E18 enter: 0 [ This 0 is required]

In E19 enter & copy down:

=IF((D19<>"")*ISNA(MATCH(D19,$D$18:D18,0)),LOOKUP(9.99999999999999E+307,E$18
:E18)+1,"")

In F17 enter:

=LOOKUP(9.99999999999999E+307,E:E)

In F18 enter: Distinct List [ which is just a label ]

In F19 enter & copy down:

=IF(ROW()-ROW(F$19)+1<=$F$17,INDEX(D:D,MATCH(ROW()-ROW(F$19)+1,E:E)),"")

You have now in column F from F19 on the list of distinct/unique
(non-duplicate) items, extracted from column D.

Now you need define a name, say DistinctList, which refers to the range
housing distinct items in column F...

Activate Insert|Name|Define.
Enter DistinctList as name in the Names in Workbook box.
Enter the following in the Refers to box:

='LO - Local Region'!$D$19:INDEX('LO - Local Region'!$F:$F,MATCH("*",'LO -
Local Region'!$F:$F,-1))

Click OK.
 
You would have to filter the original list into a new list of uniques, and
link to that. Look at Data>Filter>Advanced Filter which allows creating a
copied list of unique values. Problem is updating it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
hi aladin,
the problem is that I cannot make too extreme a chang
to the presentation of the worksheet, like you have done by adding row
and columns...

Is there no way I can say, extract the into an array, and den set th
validation to reference the array?

The important thing here is that I'd like to make the dropdown lis
from which I select dynamic, so that I need only update the reference
sheet and the selection will change automatically.

Thanks
 
nope. the the listsource for data validation MUST be:
a delimited string
OR a range object on the same sheet
OR a defined name that refers to a range
OR a defined name that refers to a delimited string CONSTANT.


but you could insert a (hidden) sheet and add Aladin's formulas there..
then point your name to that range... :)


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hi

1. Create a non-dynamic named range from your non-unique list (header
included!) + some amount of empty rows at bottom of it;
2. Add a sheet where you will have an unique item list;
3. Create an ODBC query which will retrieve unique non-empty rows from
non-unique list. Set the qurey to refresch on open;
4. Create a dynamic range based on query results table;
5. Set your data validation list source to newly defined dynamic range.
 
With a little effort, you can haul the calculations to a different sheet,
say Admin, if you cannot modify the data sheet.
BTW, you'll always need at least one extra column.
 
Back
Top