Drop down list

Greta

New Member
Joined
Oct 19, 2002
Messages
19
I wish to create a combobox or drop down list, whichever works fine. The choice chosen from the combobox should reflect in the active cell. This way it will be easier to choose the DEPT. names for each different cell than the users to type the DEPT. name manually.

Help on this is much appreciated.

Regards
Greta.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Data validation sounds like you're answer. In an out of the way place on your worksheet, create a list of all your department names. Highlight the range and name the range "DEPT". Now highlight the cells you want to have the drop down list appear in, select Validation from the Data menu, choose List from the drop down list in the Allow box, and type
=DEPT
in the source box. You can have an alert message if you wish.

That what you want?

Richard
 

Greta

New Member
Joined
Oct 19, 2002
Messages
19
Appreciate your help on this , I did try using validation at first but its not really what I want.

Reason being that the number of cells to contain the department are not determined before hand.

On some occasions one may have to choose department names in 5 different rows and some times only in 2. So visually it does not look professional to have drop down arrows in cells which will not be utilised. And what if the validation is set to 20 cells but the user adds in data which extends to 40 rows....!!!

Nevertheless, thanks for your help.

Appreciated very much,
Greta
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
I personally don't like pick from list ( data validation ), but here is a possible workaround for you. Use this for the range in the pick from list field:

=INDIRECT("a1:a"&COUNTA(A:A))

Substitute the ranges appropriately. However, it doesn't fix the "columns" issue.

_________________
Questions?<a href=http://www.excelquestions.com>http://www.excelquestions.com</a>
This message was edited by zacemmel on 2002-10-24 20:44
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
Greta
1)The drop-down list resulting from Data Validation only shows when the user selects the cell, so if they don't need to enter a dept and dont select the cell, they won't see a drop-down.
2)To save having new rows/cells that don't have Data Validation applied, make it clear to your users where data should be entered by building a "fence" at the bottom of the input range (enter "=" in the cell & copying it across will fill each cell with "=")
Apply Data Validation to all cells in the Department column including the fence cell.
Instruct users to insert new rows above the fence if they need more rows (write an "Insert More Rows" macro to do this if necessary) and the Validation will be automatically apllied to the new rows.

HTH
 

Forum statistics

Threads
1,143,914
Messages
5,721,501
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top