Drop down box in excel

[sksaini]

Hello,

I am working in excel'97. One of the pre-defined values goes into each individual cell from A1 to A50. e.g. out of a list of values like- red, yellow, green, blue- every cell has to contain one of these colours(values).

What I want is- instead of manually typing the value in each cell from A1 to A50, I want to choose from the drop down box. I have the drop down box, but don't know how to put these pre-defined values (red, yellow, green, blue) into that drop down box.

I want to choose these pre-defined values from the drop down box for every cell from A1 to A50.

Any help/suggestion or an alternative solution for this one will be highly appreciated.

Thanks.

Sushil S.

 

[dcompto]

Hope the following excerpt helps...

Microsoft Knowledge Base Article 142135
XL: How to Use the Forms Controls on a Worksheet

Drop-Down Box Example
In Excel 97 or Excel 98 Macintosh Edition, click the Combo Box button on the Forms toolbar. In earlier versions, click the Drop-Down button on the Forms toolbar.
Create an object that covers cells B2:E2.
In Excel 97 and Excel 98 Macintosh Edition, click Control on the Format menu. In earlier versions, click Object on the Format menu. Click the Control tab, enter the following information, and click OK:
To specify the range for the list, type H1:H20 in the Input Range box.
To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell Link box.

NOTE: The INDEX formula uses the value in G1 to return the proper list item.
In the Drop-Down Lines box, type 10. Ignore this step if you are using Excel for the Macintosh. This entry determines how many items will be displayed before it is necessary to use a scroll bar to view the other items.

NOTE: The 3D Shading check box is optional; it adds a three-dimensional look to the drop-down or combo box.
The drop-down or combo box should display the list of items. To use the drop-down or combo box, click any cell so that the object is not selected. When you click an item in the drop-down or combo box, cell G1 is updated to a number indicating the position in the list of the item selected. The INDEX formula in cell A1 uses this number to display the item's name.

 

[KenWright]

Use Data / Validation, change the 'any value' bit to 'List' and then in the white bar with a range selector at the end that has just appeared, type teh following in and hit OK

red,yellow,green,blue,pink_with_blue_spots

No spaces in there!!

Now you should see a drop down with your values.

To have this apply to all cells in your range, you can either:-

Do it to once cell and then copy the cell across your range, or:-

Select the entire range first before you do any of that.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Dual]

KenWright,

I have a question for you on the same subject.

I use Excel 97. Using a Data/Validation, I have created a dropdown list that contains over 300 items. Is there a way for a user to start to type and have excel use an autofill feature? I know that it will autofill for items that are already in the cells above or below, but what about just plain old autofill? MS uses the autofill feature in Access 97 dropdowns but not in Excel 97 dropdowns.
Also since my list is so large, when a user selects the dropdown it starts at the bottom of the list instead of the top. Any way to change that?
I have searched the threads but have not seen a post on this. I posted my own thread in March 2003 to no avail.


Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[PeterMoran]

Hi Doug,

Try this reference:

http://www.contextures.com/xlDataVal01.html

While it does not do exactly what you want, as far as I can see, it does have a method of having dependent lists, which you might be able to use to break your entries to the first letter and proceeding from there. Also there is an example of automatically updating the validation list which I was impressed with.

Good Luck!

Peter Moran
Two heads are always better than one!

 

[Dual]

Peter,

Thanks for the lead. I bookmarked the site for future reference, but you are correct that it will not autofill. The site does have great step-by-step for people new to data vaildation.

I get my validation list to update just by inserting a row then adding the information. This updates my list & skips the "Offset" formula for me since my list is just a list.

Thanks,

Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[sksaini]

Ken,

Your solution worked perfect for me.

I will have a look at the other problem too (just in case, I find a better solution).

Thanks.

SainiS

 

[KenWright]

Hi Dual - Can't give you any more than Peter did I'm afraid - That would have been my choice of a possible route as well.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Dreamboat]

Dual: If it becomes difficult to use Ken's method of typing all those values into that box, you can do it with a named range.

Just type your list of colors on another worksheet somewhere (you can even hide this worksheet when you're done with it). Select all the values in the list, hit Insert-Name-Define, and call it "Colors" or some such...

Then, in that box where you would've typed all the colors, just put:

=Colors

and that's it. If you later change or add colors, you may need to re-assign your named range by doing that part over, but you don't have to get into that little box.

Anne Troy

http://www.VBAExpress.com

 

[Dual]

Dreamboat,

sksaini has the color issue.

I (Dual) am looking for autofill to work with data that is validated using a named range & a dropdown. I originally posted thread68-505413 looking for you on March 20, 2003.

My named range is now up to 500. I do massive job-costing & the list grows every week. How can I get the dropdown to autofill a name from my named range without using a mouse or using the ALT key (ie when I start to type d-r-e the dropdown is searched until it finds the only dre in my named range and finds dreamboat, therefore automatically filling in the rest of your name for me)?

I understand the once your name/job is in the actually cell that the next time I type d-r-e in that same column that dreamboat will autofill, but what about the initial entry, not the second, third, etc. Does Excel 97 have this capability?




Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[xlbo]

Sorry but I don't think that functionality is available in excel - not in data validation drop downs anyway



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Dreamboat]

That's correct, xlbo.
Dual: Sorry--just put the wrong name.
For yours, Dual, I've seen people put the values that they'd WANT in a dropdown into a column, and hide the rows.

or
Access does exactly what you want.

Anne Troy

http://www.VBAExpress.com

 

[Dual]

Dreamboat,

I've tried putting the values in a column, and hiding the rows. Excel has some kind of limit to its autofill. If I put my list in the column & hide it, it still will only autofill the 35-50 closest values, not all 500 values.

We use Access to do the actual job-costing, but we use Excel as an independent double-check.

I guess I'm up the MScreek.



Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[Dreamboat]

Independent double-check...
Can't let your users into the DB with restrictions?

Anne Troy

http://www.VBAExpress.com

 

[Dual]

Per the big boss: Most of our employees share their usernames/passwords so restrictions are useless. If someone were to create absolute mayhem on the network or in crucial databases, just log in as someone else. He just likes/wants to use Excel as the summary double-check.

I don't understand why he lets this happen in the first place, but I don't own the joint. One day the price will be paid.

Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[Dreamboat]

Doug:
>>Per the big boss: Most of our employees share their usernames/passwords so restrictions are useless.

You can allow user-level permissions in Access where they only have X, Y, and Z to have access to via their machine/network login...nothing to do with a password into the DB.

Suppose your DB is on W.
Make a new DB with only certain forms you want available to these people, link the tables, and put it on M.

Don't give them access to W, only give them access to M.

They can't get into other areas of the DB if there is no facility to do so...

Sorry. But it is something you should think about. Another is putting your DB on the intranet, and giving them restrictions via web browser/asp/whatever.

Just thoughts for ya.

Anne Troy

http://www.VBAExpress.com

 

[Dual]

Boss lets them share network IDs & passwords. Boss lets them share PCs. Boss does not even log off his own PC for the weekend. Boss never updates virus defintions or IE patches. Boss is Boss. Restrictions & Security go bye-bye.

My PC = daily virus updates, firewall, user protected, locked door to office, logged off for the weekends, etc.



Doug Corbett
Dual Inc
Natick MA 01760
accounting@dual-inc.com

 

[Dreamboat]

Your boss's lack of confidentiality is causing total inefficiency and wasted manhours. Boss is stupid.
(sorry, no offense)



Have a nice Thanksgiving, all!

Anne Troy

http://www.VBAExpress.com

 

[bluedragon2]

A note to Dual...

If your company handles privacy act data, both for cutomer's or employee's, the lack of security could bring civil liabilities. If your company mishandles customer financial/credit card data....contract bids...too numerous to list type data...you could look at possible civil liabilities and in some cases federal/state laws....

Just something for your boss to think about and you if you head IT....



Blue

If I wasn't Blue, I would just be a Dragon...