How do I enter lists into names?

U

Urs Achermann

Hello everybody

Currently I am using a named range as a list for the data validation
function. To make my sheet more idiot-proof, I'd like to enter the
items from the named range directly into the name definition (via
Insert-Name-Define or VBA).

Is this possible? What syntax do I need to use?

Greetings from Switzerland
Urs
 
X

xLBaron

1st - Insert a tab and label it "Departments", that you can hid later
put the data that you
want in a column, like this:
A1= Creative
A2= Marketing
A3= HR
Just keep going down Columns "A" until you have all the departments you
want.
Now on your top Toolbar go "Insert" ~> "Name" ~> "Define"
Define Name Box will open
In the "Names in workbook" let's type "Data" for this purpose. Go to
the bottom of the box to "Refers to:" and select the data you want to
appear in the list box ... will look something like this
"=Departments!$A$1:$A$20"
Finally, on the right side of the box click "Add" and then "Okay"

2nd
Go to the tab and cell with you want the list box
Now on your top Toolbar go "Data" ~> "Validation" and "Settings" tab
In the "Allow:" box select "List" then in the "Source:" box type
=Data (if you choose another name when you defined the data then
it will be ="Your Name"

Click okay and your set.
 
U

Urs Achermann

1st - Insert a tab and label it "Departments", that you can hid later
2nd
Click okay and your set.
Click to expand...

Well, thanks for the help. Yet, this is the status quo ;)

I'd like to avoid any visible reference (I consider a hidden sheet
still as visible) to the list and enter the list elements directly into
the name definition.

Entering something like ={"one"\"two"\"three"} into "Name - RefersTo"
does yield an arry that can be accessed by index(), but it does not
work with the data validaton drop down.

Any comments?

Urs
 
Top