Drop down menu and validation over mulitiple sheets

  • Thread starter Thread starter NFaye
  • Start date Start date
N

NFaye

Hello, I am working on a workbook that has a drop down menu in cell C18 on
sheet1. The list that it references is also located on sheet1 in a hidden
column.
Each of the sheets in my workbook are identical and I am wondering if there
is quick and simple method to duplicate this function on C18 of each sheet,
as opposed to having to manually validate each separately. As well, am I
able to make each C18 reference the "list" that has been placed in the hidden
column on sheet1? Or do I have to hide this list on each sheet as well?

I don't want to link the cells to sheet1, as the repsonse on each sheet is
likely to be different.

Any help would be great. Thank you!
 
You need to have the list in every sheet. If all the sheets have the list in
the same range then Copying the validated cell from the first worksheet and
then pasting special>validation should work fine.
 
NFaye,

You can refer to the hidden list on sheet 1 from the other sheets, but you
can't do it directly...you can't refer to it as "=Sheet1!$A$2:$A$12". You
have to create a named range that refers to this data validation list, and
then in your data validation on each of the other sheets you can refer to
the name of this named range.

Assuming you hidden data validation list is in A2:A12 on sheet 1:

1. Unhiding and selecting the range will be the easiest way to give it a
name.
2. If unhidden and selected, click in the name box and type the name you
want to give it: "lstDataVal"
(no spaces, first character has to be a letter, periods/dots (.) &
underscores (_) are okay, other limitations...)
3. Also you can do Insert > Name > Define... Selected range will already be
entered in the "Refers to:" text box ready to give it a name. Type a name
and click Add/OK.
4. Now in each of your Data Validation list sources on the other sheets,
enter "=lstDataVal"

You can name the range without unhiding/selecting, but you have to manually
type in the range address...and who wants to do that. Just in case, open
the "Define Name" dialog box, manually type in the name, manually type in
the address (make sure to include the sheet name and make it an absoulute
reference), then click Add/OK.

HTH,

Conan
 
Give the list in sheet1 a name......Insert>Name>Define.

Name it MyList

In the DV list source enter =MyList

Now select the DV dropdown cell on sheet1 and copy.

Select sheet2 then SHIFT + click on last sheet.

Select C18 on activesheet and Paste.

You now have a DV dropdown in all sheets C18 referring to MyList.

Don't forget to Ungroup the sheets.


Gord Dibben MS Excel MVP
 
Back
Top