Help needed with Data Validation.

[Chris Mitchell]

I have a workbook that contains a Template sheet and several copies of it,
N01 - N0X.



I need to control what can be entered in certain cells in N01 - N0X, so need
to use Data > Validation > List. I also want users to be able to enter
their own free form text in some cells, so have to use named ranges, and
include a blank cell within the range.



I have a separate sheet 'Validation' so as to be able to only have to update
this one sheet and have the changes reflected in the drop down options in
the template and all copies.



I have played around with this and am now confused.



On the Template sheet I am able to refer to the lists on the Validation
sheet, e.g. =PSEDComments, and can change the values in the named range
PSEDComments on the Validation sheet and these changes are reflected in the
drop down options in the template.



However when I go to one of the other sheets and try to use the same
approach, i.e. select the cell, and go Data > Validation > List and enter
'=PSEDComments' as the Source it doesn't work.



Do I somehow need to refresh the sheet after having made the changes?



Is there an alternative/better way of doing this, i.e. have one sheet that
contains all of the permitted variables in various named ranges, including a
blank cell where necessary, and refer to these named ranges from other
sheets for data validation purposes?



In case I haven't explained this well enough I have put copy of a draft of
the file at

http://www.btinternet.com/~chris.a.mitchell/oddsandsods/



Look at Template sheet rows 67-69, and compare with sheet N01 rows 67-69.



P.S. I had some help with the macro on the Summary sheet and am not sure
how this works, but it appears to. I need to keep this for the finished
article.



TIA

Chris.

 

[Roger Govier]

Hi Chris

There are several named ranges showing as #Ref, and need to be re-defined
e.g Sex, Term etc.
You have set the Named ranges with a Worksheet scope, as opposed to a
Workbook scope, hence they are looking at different cells depending upon
which sheet you are on.

Personally, I would clear all of your existing named ranges.
Create each range again, with the reference being Template!range (where
range is the sets of cells belonging to that validation)

Delete Sheets N01 to N15

Apply the validation required to the relevant cells of the Template.
Copy the Template sheet 15 times (holding down control as you drag the sheet
tab to the right will create a copy), then rename these copies as N01 to N15

 

[Chris Mitchell]

Roger.

Thanks for your suggestion which is what I was thinking of doing next.

How do I tell if ranges are named with worksheet as opposed to workbook
scope?

How can I force the use of one or the other?

 

[Roger Govier]

Hi Chris

It depends on the version of Excel you are running.
In XL2007, when you use Name manager to create a name there is a dropdown
called Scope where you Select Workbook, or the specific sheet name.

In earlier versions if you define Name as !$A$1 then =Name will return
whatever is in cell A1 of the current Sheet.
If you define Name as Setup!$A$1, then =Name will always return the contents
of Sheet Setup A1, no matter what sheet you are on.

 

[Chris Mitchell]

Thanks again Roger.

Tried to use '=VALIDATION!PSEDComments' and got an error message 'You may
not use references to other worksheets or workbooks for Data Validation
criteria.' Guess this must be a limitation of Excel 2002 SP3.

Back to the drawing board.

 

[Roger Govier]

Hi Chris

No.
Insert>Name>Define> Name PSEDComments Refers to =VALIDATION!$B$13:$B$23

On Template cells A67:A69 Data Validation>List>=PSEDComments