Excel - Bespoke Dropdown Entry 2

[TortolaChris]

I have a column into which I want the user to be capable of selecting one of only a few options - by using a drop down arrow?. Is there a way of doing this without creating individual combo/list boxes?
Thanks for your help and HNY!
Chris

 

[Zathras]

Take a look at Data/Validation...

Select the cells for which you want the drop-down, then select "List" from the "Allow" combo.

Enter the valid selections in the "Source" field as a comma delimited list (e.g., 6,7,8,10)

Leave "In-cell dropdown" checked.

Explore the other tabs for options regarding the display of meaningful messages, etc.

 

[TortolaChris]

Fantastic!
Thank you so much - never used/seen/heard of this function before.
Thanks again.
Happy New Year
Chris

 

[Tonyjstone]

You could achieve your goal by using "Data - Validation"
From the 'Data' menu.
You will need to establish a 'list'of the 'permitted entries' elsewhere on the sheet, (and use that list in the 'Validation List'),
You should also set the validation to prompt the user with an appropriate message.
Then, if you set the 'validation parameters'(in Data, Validation), each time a cursor enters the object column the user will be prompted to enter a value from the 'correct' range.
(You can also prevent an incorrect entry via the same facility)

It is all very straight forward and documented in the 'Help' files.
I hope this helps.

Tonyjstone

 

[adam228]

This is a follow-up question, not an answer.

I've tried to code using macro to make a drop-down
list and the "formula1" argument refers to the list items put in a range of cells(A1:A7) in a worksheet like this way:

Sub x()
With Range("e5&quot.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=Range("A1:A7&quot
End With
End Sub

It wouldn't work. How to make the reference exactly?
Thanks for your help. I really a lot from here.

Adam.

 

[Zathras]

Adam:

Change
Formula1:=Range("A1:A7&quot

to
Formula1="=$A$1:$A$7"

Formula1 needs to be a string formula not a range. It is best to use the macro recorder to set up code for you, then copy and paste into your own macro. That will help avoid simple syntax errors like this.

Hope this helps.

 

[adam228]

Thanks for the correction.

But then how to refer(in formula1 parameter)
to the list of items which are in another sheet ?
I've tried but Excel wouldn't allow.

Also I've tried comma-delimited syntax like this:
formula1:="Teacher1,Teacher2,...,Teacher30" where
the list contains 30 items. I got an error for that
but alright when I put fewer number of items like
7 or 8. Is there any limit for list ?

Adam.

 

[Draig]

Hi Adam
To refer to a list on another worksheet, name the list (Insert, Name, Define command) and in the source box of the Data Validation dialogue box put :
=RangeName
where Rangename is the name you have assigned to the list on the other sheet.
Barborne
Worcester
UK

 

[Zathras]

Adam228:

Barbone is correct in that you should use range names. (See my earlier post in this thread.)

I responded with cell address to answer your question in the simplest way possible. However, what I should have said was that formula1 needs to be a string representation of a range not a range object.

As to referencing a range in another sheet, it is not allowed in Excel 97. However, the work-around would be to put formulas in the current sheet to point the the values you want. For example if the list "MyList" is in A1:A4 in Book2, you could replicate the list in B6:B9 in Book1 with

B6: =[Book2]Sheet1!A1
B7: =[Book2]Sheet1!A2
B8: =[Book2]Sheet1!A3
B9: =[Book2]Sheet1!A4

Then assign a new range name "MyOtherList" to B6:B9

 

[xlbo]

Zathras - don't need to do that - as long as the list is contained within a named range, it doesn't matter where it is located (although the same workbook would be handy) Rgds
~Geoff~

 

[Zathras]

Geoff - Are you using Excel 2000? It is not allowed in Excel 97.

The exact text of the message that pops up is

{X} "You may not use references to other worksheets or workbooks for Data Validation criteria."
{OK button}

 

[xlbo]

Nope - using '97
As long as the list is a named range, just use the range name instead of the range reference

instead of ='Sheet2!'$A$1:$A$10

use
=myList

sorry if that wasn't clear before Rgds
~Geoff~

 

[Zathras]

Geoff - Ok. Got it. Same workbook, different worksheet is allowed despite what the message says (as long as you use a simple range name, without the sheet qualifier).

I was trying to use separate workbooks. That's what was popping up the message. My bad -- adam228 did say "sheet" not "book"