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?

Use a combo box from the Forms toolbar.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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?

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" wrote:

Use a combo box from the Forms toolbar.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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?

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.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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" wrote:

Use a combo box from the Forms toolbar.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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?

This worked perfectly.Thanks for the help!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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" wrote:

Use a combo box from the Forms toolbar.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...

This worked perfectly.Thanks for the help!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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" wrote:

Use a combo box from the Forms toolbar.

--
Biff
Microsoft Excel MVP


"tracy" wrote in message
...
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?