Drop Down List Location Question

[travelinman]

I am using Excel 2007. I created a drop down list with the source location on
a different tab. The drop down list does not show an error message when an
entry is not part of the list. I have the error alert box checked but I still
can enter data not on the list. Any work around or is this a feature of 2007?

Thanks

 

[Roger Govier]

Hi

DV works exactly the same in 2007 as in previous versions.
Are you sure that you don't have a blank cell within your list range?

 

[travelinman]

Thanks Roger for your response.

During my research I have discovered the answer to my question. According to
Microsoft you should not be able to create a name list using letters that
correspond to column headings (I, F, DL DP, etc.). Excel uses those
identifiers for it's own use. I apparently violated that procedure and
created a name list using single and double letters. I did not receive any
error messages. The drop down list I created worked with out an error
message when other than a number on the list was entered. I renamed my drop
down list and also discovered that if there are any blank spaces in the list
Excel will not give you an error message if other data is entered. This only
seems to work when using a name list of data that is not in the current
spreadsheet.

Does Microsoft give out rewards for discovering flaws in their software (He
said tongue in cheek)?

 

[Roger Govier]

Hi

Naming ranges using values that can be confused with Column headings or Cell
references has always been something to avoid.
The problem becomes exacerbated in XL2007 because column headings range from
A through XFD so there are many 3 letter abbreviations that would be invalid
e.g. ACC, BAL, PMT etc.

The easy way around all of these problems is to prefix your name with an
underscore, then there can be no confusion.
_ACC

If there are blanks in the list for DV, then it always has allowed you to
make an entry without an error warning, whether the list is on the current
sheet or elsewhere within the workbook, in all versions of Excel.

Even if you had discovered a flaw, since I and most of the respondents in
these NG's are not Microsoft employees, giving of rewards is not within our
power!!!
I wouldn't hold your breath waiting for a reward, even if you posted a bug
or flaw directly to MS<bg>