Validation workaround?

[André]

Hi all and good luck in 2004.

I hope this is a quick an easy question. I'm using xl
2000 and have created a tracking sheet to be used by 3
employees. The workbook uses data validation to provide
drop down menus for 12 sheets in each workbook (one for
every month). I currently have to go into each monthly
sheet for all 3 employees if a new option is needed for
one of the drop down menus. I tried to put the source of
the drop down menus on one page in each of the 3 workbooks
but I get a validation error indicating that the data
cannot be on another page. Is there another way to do
this as the curent method of updating is tedious and quite
time consuming.

Thanks.

A.
/glad it's friday

 

[Peo Sjoblom]

Name the list on the other sheet, i.e. Cells A2:A10 holds a list, select the
range and do
insert>name>define and give it a name, then refer to the name as in
data>validation>allow>list

=MyList

that way you can refer to a list on another sheet

 

[Harald Staff]

Hi Andre

On "another sheet", write your list,select its cells and give it a name,
here "MyList" without quotes. One way is to write it in the address fiels
(left of A, above 1 ), another by menu Insert > Name > Define.

Then, in the validation cell, menu Data > Validation
Allow : List
Source: =MyList (Note the equal sign in front)

Now, to extend the list, just type below it and redefine the RefersTo range
in menu Insert > Name > Define.

 

[Norman Harker]

Hi Andre!

You can also refer to a range on the same sheet as the source of the
list (e.g. =A100:A104). If you want to use a list on another
worksheet you must name the list and refer to it by name (e.g.
=MyList).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Leo Heuser]

Hi André

You can have a validation list on another sheet,
but you have to give it a name to make it globally
accessible.
E.g. if your list is in F10:H20, select the range and
choose Insert > Name > Define (or similar)
In "Names in workbook" enter (e.g.) Vallist1

Now whenever you set up a validation scheme
refer to Vallist1 in allow list with
=Vallist1 (remember the equation sign)

 

[Ken Wright]

With the data on a single sheet, name the range eg MyList and then in the source
box for the DV option, type =MyList

 

[Debra Dalgleish]

If you use dynamic formulas to name the ranges, the lists is
automatically expand to include new items. There are instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

It requires a bit more work initially, but will save you time if the
lists change frequently.

 

[Ken Wright]

The other option would be to name the range dynamically using something like:-

Insert / Name / Define

Call it MyList and then in the Name refers to box, put

=OFFSET(RefData!$A$1,,,COUNTA(RefData!$A:$A))

In this example the data is all on a separate sheet called RefData and is all in
Col A. Adding new entries will automatically get picked up by the range name.

 

[André]

That's great, I had tried that last week but just couldn't
get it to work.
Now would there be a quick way to update 12 sheets as I
have 7 colums that need updating?
Thanks again.
A.

 

[Ken Wright]

See Debra's or my response for a dynamic range approach that saves you having to
redefine any ranges. Debra's has a more comprehensive reply at the end of the
link she has given you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year
----------------------------------------------------------------------------



That's great, I had tried that last week but just couldn't
get it to work.
Now would there be a quick way to update 12 sheets as I
have 7 colums that need updating?
Thanks again.
A.