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 specialvalidation should work fine.


"NFaye" wrote in message
...
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!

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








"NFaye" wrote in message
...
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!

Thats smart Conan. Thanks for teaching me something new.


"Conan Kelly" wrote in message
...
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








"NFaye" wrote in message
...
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!

Give the list in sheet1 a name......InsertNameDefine.

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


On Thu, 24 Jan 2008 12:03:01 -0800, NFaye
wrote:

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!