I have a form where I would like to click on a cell and have a dropdown
list appear for me to choose from a list of entries to fill in the
cell. This is probably an easy question but I'm stumped.


--
Bruce M
------------------------------------------------------------------------
Bruce M's Profile: http://www.excelforum.com/member.php...o&userid=31012
View this thread: http://www.excelforum.com/showthread...hreadid=506833

Seems to me you are talking about a worksheet, not a form. Is it Data
Validation you are looking for?

Data|Validation-List

Note: if your list is in another worksheet, you need to first name it.
Select the list, Insert|Names|Define, give it a name, e.g. DataList.
Then use this name in the Data Validation dialog box.

HTH
Kostis Vezerides

Thank you. Data Validation is exeactly what I am looking for.


--
Bruce M
------------------------------------------------------------------------
Bruce M's Profile: http://www.excelforum.com/member.php...o&userid=31012
View this thread: http://www.excelforum.com/showthread...hreadid=506833

I'm confused having the list in another file.

I am new to this and don't really understand "Names". Aren't names
universal in Excel? e.g. I don't have to identify the file where the list is
located, correct?

I'm doing something wrong and can't figure out what.

When I checked Help, I found "Create a drop-down list from a range of cells"
in that instruction (under #2), is the following;
1. Open the workbook that contains the list of drop-down entries.
2. Open the workbook where you want to validate cells, point to Name on
the
Insert menu, and then click Define.
3. In the Names in workbook box, type the name, for example, ValidDepts.
** 4. Accept the default value in the Refers to: box, and then click OK.
** 5. In the Refers to box, delete the contents, and keep the insertion
pointer in
the box.
6. On the Window menu, click the name of the workbook that contains the
list
of drop-down entries, and then click the worksheet that contains
the list.
7. Select the cells containing the list.
8. In the Define Name dialog box, click Add, and then click Close.
I think a step is missing between steps 4 and 5... HELP
--
Glenda


"vezerid" wrote:

Seems to me you are talking about a worksheet, not a form. Is it Data
Validation you are looking for?

Data|Validation-List

Note: if your list is in another worksheet, you need to first name it.
Select the list, Insert|Names|Define, give it a name, e.g. DataList.
Then use this name in the Data Validation dialog box.

HTH
Kostis Vezerides

Hi Glenda,

I just saw your post. I reviewed the list of instructions and there is
nothing missing. You just have to be very careful in following the
steps until your task is performed.

A few things about range names, which might help you:

All cells have a priori names. A1, B12 etc. Since in every worksheet we
have similar grids (A1:IV65536), the name A1 for example IS NOT
universal. Within the workbook we distinguish with this syntax:
Sheet1!A1 and Sheet2!A2. But other workbooks might have similarly named
sheets. So a full specification would be something like
[MyWorkbook]Sheet1!A1.
When in a worksheet you want to refer to a cell in the same worksheet,
A1 is enough. But if you want to refer to a cell in another worksheet
you have to use the more elaborate syntax above.

It is possible to give a user-defined name to a single cell or range.
For example, if you name cell C12 as Sales, from this point on, the
following two formulas are equivalent:
=2*C12
=2*Sales

The Refers To: box in Insert|Name|dDefine has to do exactly with the
cell (or range) to be named.

There are more things to say about names but I hope this introduction
clarifies the basics the task you want to accomplish

HTH
Kostis Vezerides

I try to follow all instructions "cook book style" - I am having difficulty
specifically between steps 4 and 5 (under number 2) in the previous post.

** 4. Accept the default value in the Refers to: box, and then click OK.
** 5. In the Refers to box, delete the contents, and keep the insertion
pointer in the box.

In step 4, I click OK and the Refers to box and the window closes. Where is
Step 5 completed, in the worksheet where I want to put the list or the file
with the list? Also, what criteria do I choose at 'insert / name '?

Thank you so much for your help.

Glenda


"vezerid" wrote:

Hi Glenda,

I just saw your post. I reviewed the list of instructions and there is
nothing missing. You just have to be very careful in following the
steps until your task is performed.

A few things about range names, which might help you:

All cells have a priori names. A1, B12 etc. Since in every worksheet we
have similar grids (A1:IV65536), the name A1 for example IS NOT
universal. Within the workbook we distinguish with this syntax:
Sheet1!A1 and Sheet2!A2. But other workbooks might have similarly named
sheets. So a full specification would be something like
[MyWorkbook]Sheet1!A1.
When in a worksheet you want to refer to a cell in the same worksheet,
A1 is enough. But if you want to refer to a cell in another worksheet
you have to use the more elaborate syntax above.

It is possible to give a user-defined name to a single cell or range.
For example, if you name cell C12 as Sales, from this point on, the
following two formulas are equivalent:
=2*C12
=2*Sales

The Refers To: box in Insert|Name|dDefine has to do exactly with the
cell (or range) to be named.

There are more things to say about names but I hope this introduction
clarifies the basics the task you want to accomplish

HTH
Kostis Vezerides

Forget then step 4. Just select everything that might be appearing in
the Refers To: box, and then move to the other window and select the
range. This has the effect that as you are moving about and selecting,
the reference in the box changes to reflect your current selection.

Does this work?

Kostis

No it does not work. I still don't know what I am doing wrong, I've tried
everything I can think of.

I am trying to use the name I created in one file as the 'drop down list' in
another file. I get "you may not use references to other worksheets or
workbooks for data validation". This despite the instructions in "Create a
drop-down list from a range of cells" #6. I am preceding with an equal sign
and still the error. Any ideas?
--
Glenda


"vezerid" wrote:

Forget then step 4. Just select everything that might be appearing in
the Refers To: box, and then move to the other window and select the
range. This has the effect that as you are moving about and selecting,
the reference in the box changes to reflect your current selection.

Does this work?

Kostis

I found the correct step-by-step at the site below. I think the problem was
that I didn't keep the file with the list open... somehow I missed that part
of the instruction. Thanks for your help.

http://www.contextures.com/xlDataVal05.html

--
Glenda


"Glenda" wrote:

No it does not work. I still don't know what I am doing wrong, I've tried
everything I can think of.

I am trying to use the name I created in one file as the 'drop down list' in
another file. I get "you may not use references to other worksheets or
workbooks for data validation". This despite the instructions in "Create a
drop-down list from a range of cells" #6. I am preceding with an equal sign
and still the error. Any ideas?
--
Glenda


"vezerid" wrote:

Forget then step 4. Just select everything that might be appearing in
the Refers To: box, and then move to the other window and select the
range. This has the effect that as you are moving about and selecting,
the reference in the box changes to reflect your current selection.

Does this work?

Kostis