Excel 2003 Drop Down Lists on Multiple Sheets

Question

Hello

I'm looking for a little help please.  I use a workbook to track progress of tasks over periods of time.  There are worksheets for each month, and each task is entered on a new row.  There are columns for the various phases of the tasks, with drop down lists in to select your initials to mark the work as complete.  I have written macros to change the cell colour from red to green when you enter the initials.  In Excel 2003 (have no choice over this version as it is a work computer system) the drop down list data has to be on the same sheet as the cells.  But what I'd rather have is a single admin sheet where I can update the list of initials as the work force changes.  I thought I could do this simply by making the list on each sheet reference the list on the admin sheet (eg cell A10 is =admin!$a$10 B10 is =admin!$b$10 etc) but then when I change the data on sheet admin, the drop down lists have #reference in them.

Is there a simpler way of doing this?  It may require some macro work that is beyond my basic skill set.

All thoughts and ideas very welcome!

Thank you

Answer 1

You stated. . . . ."In Excel 2003 the drop down list data has to be on the same sheet as the cells. "

If you give your DV List source range a Name you can refer to that name from other sheets.

See the video here at Debra Dalgleish's site

http://www.contextures.on.ca/xlDataVal01.html#differentsheet

Gord

Answer 2

You're welcome.

I have been coming here for years to learn about Excel.

Gord

Answer 3

Gord

Sorry for a delay in replying - but THANK YOU!  You have solved my problem and I have learnt more about Excel!

Many thanks again

Answer 4

Thanks Gord - I'll give that a go tomorrow.  My problem might have been trying to enter the source by trying to type the sheet and cells in eg admin!$a$2:$a$5 (since clicking on the relevant sheet wouldn't work)!

V grateful for your time and support.