Drop down box confusion - Dave??

Posted by George A on April 05, 2001 2:08 PM

I read a post 3/13/01 from Marilyn regarding
creating drop down boxes (and other posts that also
mentioned them).

One tip was to leave blanks in the named range which
would allow you to add to the list later BUT when i set
up the error message to appear when an invalid entry
is made it will not work. It only pops up when I set
the named range to include only the current number
of items.

Also, is there a way using drop down boxes that if the
user started typing 'Mar' the cell would be automatically
completed with 'Marketing' (if that was in the dropdown
box)?

thanks in advance

Posted by Dave Hawley on April 05, 2001 2:21 PM

Hi George

There does seem to be a problem with Validation and a named range that includes blanks. The way to evercome this however is to use a Dynamic Range. You can see some examples of these on my Website under the link "Dynamic Ranges".

To have the entry auto complete to a match in the list you will need to use a ComboBox from the "Control ToolBox".
Then in it's propeties Window set its "MatchEnty" to "fmMatchEntryComplete" its "ListFillRange" to the name of your Dynamic range and its "LinkedCell" to the cell you want the selection entered in.

Dave

set it's "MatchEntry"

OzGrid Business Applications

Posted by George A on April 05, 2001 3:47 PM

THANKS DAVE. The dynamic range works great.

Although the control box works for one cell at a
time, what I am looking for might not be possible.
Let me try to explain more.
I have a user inputting report titles and would
like to make it easier on them and let them put
in a couple of letters instead of making them type
the whole title.

They would be inputting 20 or 30 title at a time.
Some titles are the same and some different.
any thoughts?

thanks again

Posted by Dave Hawley on April 05, 2001 4:11 PM

George, glad the dynamic range worked for you. They can be very handy and can be manipulated to suit most needs. They are great for charts and Pivot Tables.

If you want the user to type in an cell and have them only type a few letters, try going to Tools>Auto correct and replace the most common ones with abreviations. Then maybe create a small table they can see easily with the abreviations. Or maybe use the Data>Validation message they see when they select the cell.

Another feature of Excel you may not be aware of is, if you have a list of text above or below a selected cell, you can right click and then on the shortcut menu choose "Pick from list" this can save a lot of typing. Excel will also automatically complete words you type in a cell if it already has been typed in the cells above in the same column. Just be sure the feature is tuned on under Tools>Options|Edit-AutoComplete for cell values.

Now you can use a little trick to make life easier for the user. Put all you words you want in say cells A1:A30 now hide these rows, when the user starts typing in cell A31 Excel will auto complete the word, this will keep working if they then drop down to cell A32 and so on.

Dave
OzGrid Business Applications

Posted by George A on April 05, 2001 4:52 PM

Thanks again. I have pondered all of those and just need to decide
which is best. The drop down boxes and the pick
lists have limits of what they show and it makes the
user scroll to find what they need. The autocomplete
might be the best option, but just have to make somewhat
unique entries to limit the user input.

Although autocorrect lets them put in an abbreviation
and then it will put the whole title. Kinda elliminates
an extra column and a lookup formula. hmmm.

so many options

thanks

Posted by Eni on May 19, 2001 9:57 AM

Everything you can do with Dropdown boxes Jav Scripts ASP MYSQL etc

Webmaster resource Engine

click the link below

would