Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am using Excel 2003 and am doing a drop down list using Data|Validation. However, I must have the data for my drop down list in the same sheet as my drop down list, otherwise the drop down lsit doesn't work. Am I doing something wrong? I used to be able to put the data in a different sheet. What happened?

Valmont
 
Valmont

It's OK to have the list in another sheet,
but then you must name it, e.g. "List1"
(without quotes), and use that name in
the validation box (Allow: List and enter
=List1)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Valmont said:
Hello,

I am using Excel 2003 and am doing a drop down list using Data|Validation.
Click to expand...
However, I must have the data for my drop down list in the same sheet as my
drop down list, otherwise the drop down lsit doesn't work. Am I doing
something wrong? I used to be able to put the data in a different sheet.
What happened?
 
Valmont

It's OK to have the list in another sheet,
but then you must name it, e.g. "List1"
(without quotes), and use that name in
the validation box (Allow: List and enter
=List1)
Click to expand...

G'day,

Is this a new feature in Excel2003? I can't get a validation list from
another sheet to work in either 2000 or XP; I've tried single quotes around
the sheet name (so have to copy the list to the sheet I'm want validation
in).

Cheers,
Julian

--
Julian Mattay, email: julian x mattay z csiro x au
(where x -> dot, z -> at)
Local IT Bloke
CSIRO, Forestry and Forest Products Ph: +61 8 8721 8118
Mt Gambier, South Australia, Australia Fax: +61 8 8723 9058
 
Name the range, assume it is Sheet2!A2:A20, so select A2:A20 and either do
insert>name>define and give it a name
(or just type a name in the namebox). Then in sheet1 use the list as follows

data>validation>allow>list and in the source box type =MyList where MyList
is the name you gave the range.
Works for excel 97 - 2003
 
Dave Peterson said:
It's been there since xl97 (when Data|Validation was added).

You may want to review some notes/pictures at Debra Dalgleish's site:
http://www.contextures.com/xlDataVal05.html

(a picture is worth 1000 words.)
Click to expand...

Thanks Dave and Peo, it works (I must have got scared off by the previous
"May not use references to other worksheets" message)

Cheers,
Julian

--
Julian Mattay, email: julian x mattay z csiro x au
(where x -> dot, z -> at)
Local IT Bloke
CSIRO, Forestry and Forest Products Ph: +61 8 8721 8118
Mt Gambier, South Australia, Australia Fax: +61 8 8723 9058
 
Back
Top