Drop down list help

[tracy]

I have created a simple drop down list. I would like the drop down arrow to
appear in the spread sheet all the time, without it being necessary to select
the cell. Is this possible?

 

[T. Valko]

Use a combo box from the Forms toolbar.

 

[tracy]

OK, I got this far. Now the question is, How do I get the text to show that I
have selected in the drop down list, as oppose to a value? (1,2,3...)

 

[T. Valko]

Ok, let's assume the source range for the combo box is I1:I10.

The linked cell is A1.

Use this formula to get the actual selection:

=IF(A1="","",INDEX(I1:I10,A1))

This is one of the drawbacks of using a combo box. You have to use an extra
step to be able to actually use the selection. But.....if you want the drop
arrow always visible this is what you have to do.

A little trick is to put this formula and/or the linked cell in a cell
that's under the combo box. That way it "appears" to work just like a data
validation drop down list. All you'll see is the selection in the combo box.
Here's a screencap to show how this would work:

http://img209.imageshack.us/img209/9852/comboboxjw9.jpg

In the screencap I use B3 for the linked cell and C3 contains this formula:

=IF(B3="","",INDEX(I1:I10,B3))

As you can see the entries in both of those cells, B3:C3, are hidden because
the combo box "sits" on top of them. Then, if you want to use the selection
from the combo box in a formula you just need to refer to cell C3.

 

[tracy]

This worked perfectly.Thanks for the help!

Ok, let's assume the source range for the combo box is I1:I10.

The linked cell is A1.

Use this formula to get the actual selection:

=IF(A1="","",INDEX(I1:I10,A1))

This is one of the drawbacks of using a combo box. You have to use an extra
step to be able to actually use the selection. But.....if you want the drop
arrow always visible this is what you have to do.

A little trick is to put this formula and/or the linked cell in a cell
that's under the combo box. That way it "appears" to work just like a data
validation drop down list. All you'll see is the selection in the combo box.
Here's a screencap to show how this would work:

http://img209.imageshack.us/img209/9852/comboboxjw9.jpg

In the screencap I use B3 for the linked cell and C3 contains this formula:

=IF(B3="","",INDEX(I1:I10,B3))

As you can see the entries in both of those cells, B3:C3, are hidden because
the combo box "sits" on top of them. Then, if you want to use the selection
from the combo box in a formula you just need to refer to cell C3.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[SGfla]

I wonder if someone can help me.....I have inserted a drop down box in my
spreadsheet and I want to have a blank line that allows the user to enter
info (info that may not be on the drop down menu).....is this possible? If
so, how do I do it?
I would appreaciate any help in this matter!
Thanks!

 

[Gord Dibben]

If a Data Validation Dropdown you don't need a blank.

Under most conditions simply turn off the Error alert and user can type anything
into the cell.

For more info on not "most" conditions see Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal08.html#Invalid

That entry will not become a permanent part of the list.

For that you would need VBA.

See Debra's site for a sample workbook.

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0012 - Update Validation List -- type a new value in a cell that contains data
validation, and it's automatically added to the source list, and the list is
sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb


Gord Dibben MS Excel MVP

 

[SGfla]

Thank you for the info. It helped!

If a Data Validation Dropdown you don't need a blank.

Under most conditions simply turn off the Error alert and user can type anything
into the cell.

For more info on not "most" conditions see Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal08.html#Invalid

That entry will not become a permanent part of the list.

For that you would need VBA.

See Debra's site for a sample workbook.

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0012 - Update Validation List -- type a new value in a cell that contains data
validation, and it's automatically added to the source list, and the list is
sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb


Gord Dibben MS Excel MVP

 

[SGfla]

Ok, now I have a different problem. I have created a drop down box but the
info I'm using is on the same worksheet, but I want the info for the drop
down box to be on a different page in the same worksheet. I tried using the
instructions listted:

"If you want to use another worksheet or another workbook, do one of the
following: Use a different worksheet in the same workbook Type the list on
that worksheet, and then define a name (name: A word or string of characters
that represents a cell, range of cells, formula, or constant value. Use
easy-to-understand names, such as Products, to refer to hard to understand
ranges, such as Sales!C20:C30.) for the list.
How?
Select the cell, range of cells, or nonadjacent selections (nonadjacent
selection: A selection of two or more cells or ranges that don't touch each
other. When plotting nonadjacent selections in a chart, make sure that the
combined selections form a rectangular shape.) that you want to name.
Click the Name box at the left end of the formula bar (formula bar: A bar at
the top of the Excel window that you use to enter or edit values or formulas
in cells or charts. Displays the constant value or formula stored in the
active cell.).

Type the name for the cells, for example, ValidDepts.
Press ENTER.
Select the cell where you want the drop-down list.
On the Data menu, click Validation, and then click the Settings tab.
In the Allow box, click List.
To specify the location of the list of valid entries, do one of the
following:
If the list is in the current worksheet, enter a reference to your list in
the Source box.
If the list is on a different worksheet in the same workbook or a different
workbook, enter the name that you defined for your list in the Source box.
In both cases, make sure that the reference or name is preceded with an
equal sign (=). For example, enter =ValidDepts.

Make sure that the In-cell drop-down check box is selected.
To specify whether the cell can be left blank, select or clear the Ignore
blank check box."

But after following those steps it did not put the list I had selected and
named....it only put what i named the list with an equal sign after it
(vendors=).

Can someone please tell me how to do this? I'm pulling my hair out with
this project!!!!!
Thanks!
SGfla.

 

[SGfla]

I meant "different page in the same workbook"

 

[Gord Dibben]

Actually you meant different "worksheet" in the same workbook.

Workbook don'ts have "pages".


Gord

 

[Gord Dibben]

Did you name the listrange on the second sheet as "vendors"(no quotes) referring
to your list of items?

In the DV sheet did you enter =vendors in the source dialog?


Gord

 

[Gord Dibben]

Sheesh!!

 

[SGfla]

Gord,
Thank you very much for your help! That is exactly what I needed and it
worked!
SGfla

 

[Gord Dibben]

Good to hear.

Thanks for the feedback


Gord

Gord,
Thank you very much for your help! That is exactly what I needed and it
worked!
SGfla