Drop down list help
 

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?

Drop down list help
 

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?

Drop down list help
 

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?

Drop down list 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.

--
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?

Drop down list help
 

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?

Drop down list help
 

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?

Drop down list help
 

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!

Drop down list help
 

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

On Tue, 1 Jul 2008 11:44:04 -0700, SGfla
wrote:

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!

Drop down list help
 

Thank you for the info. It helped!

"Gord Dibben" wrote:

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

On Tue, 1 Jul 2008 11:44:04 -0700, SGfla
wrote:

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!

Drop down list help
 

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.

Drop down list help
 

I meant "different page in the same workbook"

"SGfla" wrote:

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.

Drop down list help
 

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

Workbook don'ts have "pages".


Gord

On Wed, 2 Jul 2008 11:20:01 -0700, SGfla
wrote:

I meant "different page in the same workbook"

"SGfla" wrote:

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.

Drop down list help
 

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


On Wed, 2 Jul 2008 11:18:01 -0700, SGfla
wrote:

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.

Drop down list help
 

Sheesh!!



On Wed, 02 Jul 2008 14:04:40 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Workbook don'ts have "pages".

Drop down list help
 

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

"Gord Dibben" wrote:

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


On Wed, 2 Jul 2008 11:18:01 -0700, SGfla
wrote:

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.

Drop down list help
 

Good to hear.

Thanks for the feedback


Gord

On Thu, 3 Jul 2008 04:28:00 -0700, SGfla
wrote:

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

"Gord Dibben" wrote:

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


On Wed, 2 Jul 2008 11:18:01 -0700, SGfla
wrote:

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.