Autoselecting values (2000)

Topic

New Reply

Is is possible to include a list of some sort where l can choose from a selection.
i.e in the screenshot l keep having to type in the date and version number all the type as l do this on a regular basis it is very repetitive.
I would like to choose the version number and date rather than keep having to type them in all the time.
I already have a drop down which is an autofilter, this needs to be on at all times.

Reply | Quote

Replies

You can use Data | Validation with the List option.

Reply | Quote

I am not to sure how you use this option please can you explain in a bit more detail, thanks.

Reply | Quote

Check out MS MVP Debra Dagliesh’s Data Validation – Basics

Steve

Reply | Quote

Start by creating lists of allowed values: on another part of the worksheet, type a list of version numbers, and a list of dates.

Next, select the cells in which you want to enter a version number.
Select Data | Validation.
Select List from the Allow dropdown.
Click in the Source box.
Point to the list of version numbers.
Activate the Input Message tab.
If you wish, enter a message that will be displayed when you select one of the cells.
Activate the Error Alert tab.
If you want to be able to select ONLY values from the list, set the Style to Stop.
Enter an appropriate error message.
Click OK.

Repeat for the cells in which you want to enter dates.

Reply | Quote

I have done this but don’t know how to select a value from the lists l have created?
Please can you tell me how l do this, thanks.

Reply | Quote

Click in the cell where you want to enter a version number or date.
A dropdown arrow should appear to the right of the cell.
Click on the dropdown arrow to make the list appear.
Click on the item you want.

Reply | Quote

I have tried this but l cannot see the dropdown, see screenshot.

Reply | Quote

I have got the drop down list working but for some reason it is not showing the correct values.
see screenshot.It should show all the dates in the column specified.

Reply | Quote

See the attached sample file.

Where are you getting stuck JJ? Do you need more help?

Reply | Quote

I cannot get the dropdown to appear please can you tell me what l am doing wrong in order for this not to appear?

Reply | Quote

Could you attach an example file that does not work correctly so we can see what you set up?

Steve

Reply | Quote

I have now geot it work , l simple copied the attached example, thanks for your help.

Reply | Quote

OK, lets start from the beginning again…(I will use the date drop down example)

1. Create a list of dates in the Z column ( for example ). Select Z1 and type Jan 2006. Select Z2 and type Feb 2006. Select both these typed dates and autofill (the black handle on the bottom corner of the selection) the selection to create a list of dates…say to Dec 2007.

Now that you have created the list of dates that will go into the drop down do the following:

1. Select the range of cells where the drop down arrow must appear. For example, select the cells C2:C100 if each cell in this range must have a drop down that will contain a date.
2. Once the range is selected, choose Data | Validation
3. From the Allow drop down, choose List
4. Click in the source box that appears
5. Scroll to cell Z1 and select the list of dates you created so that the reference Z1:Z24 appears in the source box.
6. Click on the error alert tab and type a message similar to: The value you selected is not valid, please select from the list!
7. Choose OK

The cells in the C column should now have an arrow appear on the right when you click to select them. IE: Cell C2 will now display an arrow when you select it. Click on the arrow and you should see your list of dates appear.

I hope these instructions will help you!

Reply | Quote

Thats great thanks.

Reply | Quote

Using Data | Validation, choose List from the Allow drop down.
In the source box, type the values you want in the list. If the values are dates, you could insert a new sheet and type the current date and auto fill as far as you need to. Then select the date range you created as the source for the list box. (It is not a good idea to have too many dates to choose from!)
Then in the other tabs, you can choose to include an error msg, if the person types and invalid value!

PS: About the date field. You can use the key combination of : CTRL + ; (Semi Colon) to insert the current date very quickly without typing!

Reply | Quote