Allow other values other than ones in Value List

Is there a way i can be permitted to add values to a drop down value list of 
a cell?
If the value is not in the list at present i get a "The value you entered is 
not valid" error.
I do not want to add that particular value to the list each time as it may 
never be used again, and the list would grow beyond the never never....

Any idea's? 


0
5/30/2006 12:07:29 AM
excel 39879 articles. 2 followers. Follow

5 Replies
473 Views

Similar Articles

[PageSpeed] 6

Corey, if this is from data validation uncheck show error alert on the error
alert tab in data validation

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Corey" <coreymcconnell@splicetech.com.au> wrote in message
news:%237FjL03gGHA.4864@TK2MSFTNGP05.phx.gbl...
> Is there a way i can be permitted to add values to a drop down value list
of
> a cell?
> If the value is not in the list at present i get a "The value you entered
is
> not valid" error.
> I do not want to add that particular value to the list each time as it may
> never be used again, and the list would grow beyond the never never....
>
> Any idea's?
>
>


0
5/30/2006 12:17:23 AM
Try this:

Select the cell(s) to be impacted by this change

From the Excel main menu:
<data><validation><error alert tab>
(assuming you already have the validation part working)
Set the Style to: Warning or Information (instead of Stop)
Set the warning message (eg "That item is not on the list...continue?")

That way, non-list entries will trigger a warning, but will still be 
accepted if the user chooses.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Corey" wrote:

> Is there a way i can be permitted to add values to a drop down value list of 
> a cell?
> If the value is not in the list at present i get a "The value you entered is 
> not valid" error.
> I do not want to add that particular value to the list each time as it may 
> never be used again, and the list would grow beyond the never never....
> 
> Any idea's? 
> 
> 
> 
0
5/30/2006 12:18:01 AM
Are you using Data|Validation.

If you are, you can change the message/prompt and the style (from Stop to
Warning or Information):

Data|Validation|Error Alert tab.



Corey wrote:
> 
> Is there a way i can be permitted to add values to a drop down value list of
> a cell?
> If the value is not in the list at present i get a "The value you entered is
> not valid" error.
> I do not want to add that particular value to the list each time as it may
> never be used again, and the list would grow beyond the never never....
> 
> Any idea's?

-- 

Dave Peterson
0
petersod (12005)
5/30/2006 12:20:21 AM
Thanks Paul B.
Simple as that. ERROR ALERT was ON.
I should get in the practice of reading ALL the Tabs on windows.


Cheers

Corey....
"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
news:elnV153gGHA.4368@TK2MSFTNGP03.phx.gbl...
> Corey, if this is from data validation uncheck show error alert on the 
> error
> alert tab in data validation
>
> -- 
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Corey" <coreymcconnell@splicetech.com.au> wrote in message
> news:%237FjL03gGHA.4864@TK2MSFTNGP05.phx.gbl...
>> Is there a way i can be permitted to add values to a drop down value list
> of
>> a cell?
>> If the value is not in the list at present i get a "The value you entered
> is
>> not valid" error.
>> I do not want to add that particular value to the list each time as it 
>> may
>> never be used again, and the list would grow beyond the never never....
>>
>> Any idea's?
>>
>>
>
> 


0
5/30/2006 12:24:31 AM
your welcome, thanks for the feedback

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Corey" <coreymcconnell@splicetech.com.au> wrote in message
news:ua$xs93gGHA.1276@TK2MSFTNGP03.phx.gbl...
> Thanks Paul B.
> Simple as that. ERROR ALERT was ON.
> I should get in the practice of reading ALL the Tabs on windows.
>
>
> Cheers
>
> Corey....
> "Paul B" <to_much_spam_to_list@nospam.com> wrote in message
> news:elnV153gGHA.4368@TK2MSFTNGP03.phx.gbl...
> > Corey, if this is from data validation uncheck show error alert on the
> > error
> > alert tab in data validation
> >
> > -- 
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> > "Corey" <coreymcconnell@splicetech.com.au> wrote in message
> > news:%237FjL03gGHA.4864@TK2MSFTNGP05.phx.gbl...
> >> Is there a way i can be permitted to add values to a drop down value
list
> > of
> >> a cell?
> >> If the value is not in the list at present i get a "The value you
entered
> > is
> >> not valid" error.
> >> I do not want to add that particular value to the list each time as it
> >> may
> >> never be used again, and the list would grow beyond the never never....
> >>
> >> Any idea's?
> >>
> >>
> >
> >
>
>


0
5/30/2006 2:31:01 AM
Reply:

Similar Artilces:

Compare two file/colunms, hide row not does not equal list.
Excel 2000 -have two files. File "A" has 1 through 4155 records (rows with three columns) in numerical order. File "B", has a list of almost 200 rows/numbers (one column) from file "A". How can I hide the rows in file "A", not are listed in file "B"? Thanks, Jerry Not sure whether you are still monitoring this post, Jerry. Anyway, here's some thoughts ventured .. Conceptually, you should be able to achieve this via setting up a helper col and then autofiltering on the helper. Let's start by simplifying the scenario by having bo...

Virtual list ctrl and LVIF_PARAM
Hi, This is from MSDN documentation on virtual list control. "...Virtual list controls maintains very little item information. Except for the item selection and focus information, all item information is managed by the owner of the control. Information is requested by the framework via a LVN_GETDISPINFO notification message..." "...In the handler for the LVN_GETDISPINFO notification message, you must check to see what type of information is being requested. The possible values are: ... - LVIF_PARAM The lParam member must be filled in. ... You should then supply whatever...

Mail to more than one address
When I send email (invoices)through my accounting program to more than one recipient, I get the following; Message could not be sent because one of the recepients was rejected by the server. Server response 5015.54 syntax error in parameter scanning. Does anyone know what this means? one of the addresses you are trying to send to is bad - examine the email addresses for errors. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide)...

how do I get more than one sub-total in pivot tables?
I am using pivot tables to show summary HR recruiting data. The data columns are nested at three levels - priority(a, b or c), type(add/replace), number of reqs and number of positions. The pivot table automatically gives me sub-totals within the priority so I get number of reqs that are adds of priority A for example. I also automatically get a total of number of reqs and total of number of positions. What I'm trying to get is the sub-total of number of reqs that are adds regardless of priority. Move Type to the column area, and you'll get columns with totals for Add and...

One paystub, two paychecks
I work for the U.S. Navy and therefore receive my paychecks from Defense Finance and Accounting Service (DFAS). The way DFAS pays me is like this: -On the first day of the month, DFAS projects how much I'll earn that month in base pay, how much I'll pay in taxes, additional deductions/allotments, and then comes up with my Net Pay for the month. Pretty straightforward. -On the 15th of the month, DFAS pays me -half- of my projected Net Pay for that month. -On the last day of the month, DFAS pays me the other half. At that point I get a paystub which reflects my income, taxes, ...

Problem reading with Percentage values
Hi, I am using OleDB provider to read excel file. I am facing the problem when reading percentage values from some excel sheets (very rarely). Suppose a cell has 42.3%, when reading the value it comes like 0.423... but while reading other Workbook's excel sheets (for those actual value is 24.7%), it reads properly like 24.7.... When i checked excel sheet cell Format for both Workbooks, it is same with Percentage with 1 decimal. Connection String i am using to read excel sheet is Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + strFilePath.R...

Lookup Value in Workflow Template
I was trying to get lookup value in my workflow template (Email), but i could not the value appearing null (empty), while Picklist or text its appearing in my worfklow email template. why i am not getting any lookup value. Example : Owner (its not appearing). Time Create : Created on (Its appearing). early response will be much appriciated. Thanks, Syed. ...

Cell format
I am working on an expenses spreadsheet, and I have the category of expenses in column A. In the cells that would carry the numerical values, how do I format the cell to show a hyphen where a value would eventually be, indicating that the present value in the cell is zero? Maybe this....... Format for Custom, then _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) or _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) The cell must have a zero in it for the hyphen to show up. Vaya con Dios, Chuck, CABGx3 "sheana" wrote: > I am working on an expenses spreadsheet, and ...

How to display a cell value as a text entry in another cell?
What does one call this? I can't find anything because I don't know how to describe what I'm trying to do <g>. In a financial spreadsheet I'm overhauling, I found something that can be finetuned to make life less difficult. Currently in a cell, cell E23, they have this text description for 'mileage': @ 0.45/km Yet they have a cell for the English display of that information that a subsequent formula in I23 draws its information from, cell E62, which shows as $0.45, and is out of the print area. That's the cell you upate for the formulas to be current. R...

Known Email Address in distribution list bounces back
Hi, I've got an address that is an external contact in Exchange as a member of a distribution group. The email address is definately a good one as single emails to that address get through ok, but anything sent to the distribution list gives the sender a bounceback from the contact's address. Anyone got any ideas? Many thanks Rich =?Utf-8?B?TWNEaXRjaA==?= <McDitch@discussions.microsoft.com> wrote in news:FE088019-435A-4864-8607-4F3BD4812A16@microsoft.com: > I've got an address that is an external contact in Exchange as a > member of a distribution group. The e...

Pulling data from individual files to master list
Hello, I've just been entering the world of Excel for the past few months, as I started a new job last year and my main duty is to bring the company into the 21st century (or even the late 20th, at this point). What I'd like to do sounds a little backwards, but I think it's the way to go, if it's possible: I'm creating individual sheets for our products, so that all the relevant info for, say, product A001 is shown on one sheet named "A001.xls". But I would also like to create a "master" list. I say "master" in quotes because it's not r...

Create task and sub-task lists using the calendar
Looking for the best way to organize 10 task subjects. Some having as many as 5 sub-tasks, as well as creating to do lists for 4 employees. I set these up in the Folder List and would like to drag these into the Calendar allowing me to print each person a To Do List. A friend suggested Categorizing ,but I like having the folders all visible in the Outlook Shortcuts. Any help would be appreciated. ...

When I Select More Than One Tab
Is there a way for Excel to tell me when I have more than one tab selected? Other than me remembering to look at the top and see [Group] in the file name. Any ideas are welcome! (B^>)-]=[ The grouped sheets' tabs will be white. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 15:20:51 -0400, "WSR" <wsr-203@hotmail.com> wrote: >Is there a way for Excel to tell me when I have more than one tab selected? > >Other than me remembering to look at the top and see [Group] in the file >name. > >Any ideas are welcome! > >(B...

Transfer data from one sheet to another
Did I stump you guys? This is a hard one! Here is the senario: Worksheet one has a cell with a drop down menu with the numbers 1-10 in it. Below the drop down menu are two columns labled 'account number' and 'method' each with many fields available for info below them. Worksheet 2 is a database. It has many columns labled 'account number', ' method', 'date', 'cycle' etc. The cycle field is what contains the numbers 1-10 of the previous worksheet. I need a way of making it so that when I choose 1-10 from the dropdown menu on the fir...

Distribution List error #2
I have Exchange running as part of a Small Biz Server. When we try to create a DL of external address's (like 180 addresses) we get an error saying that we have exceeded the max size of the DL. I can't find anything that provides any indication of a limit such as this. Thanks again, Mark Semans 180 certainly isn't the limit of a multi-valued attribute in AD.... Did you try creating another DL to confirm it wasn't specific to that particular object? -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog -------------------------...

CRM
Hi, I've got a problem to import data into drop down list (CRM). I alreday try cdf tools. But, when I execute the "p_cdf_PopulateStringMap" nothing happens! The cdf_StringMap is not completed... Help! someone has an idea to resolve my problem ? thanks regards, When you have installed the Data Migration Framework, you have also got 2 executables. You can (probably) find them in this folders: c:\program files\microsoft crm data migration tool\cdf c:\program files\microsoft crm data migration tool\cdfmigration one of the tools is called initializecdf.exe. Run this tool and...

How to create my own generic list?
I'm still new at C# or any OO program and my coworkers are all at a seminar this week. How do I create my own "generic list" that implements all the features of the .net generic list<string> plus a method to fill itself from strings in an external file. Is there a simple template somewhere I could start with? Do I just inherit from generic list and add my method? thanks, LJB "LJB" <postmaster@127.0.0.1> wrote in message news:O%23EBRGuuKHA.1796@TK2MSFTNGP02.phx.gbl... > I'm still new at C# or any OO program and my coworkers a...

Canadian CRM Discussion List
Hi all: If anyone working with CRM is located in Canada, please let me know as I would like to get some discussion groups going. Thanks. John. John Feel free to ask any crm questions here as we have an international audience "John Straumann" <jstraumann@cogeco.ca> wrote in message news:eMioB96qDHA.2520@TK2MSFTNGP09.phx.gbl... > Hi all: > > If anyone working with CRM is located in Canada, please let me know as I > would like to get some discussion groups going. > > Thanks. > > John. > > ...

Go to specific line in numbered list?
Hey all, I have a question that will hopefully save me a lot of time! I'm working on my dissertation, and my data (transcription of speech) is formatted in a very long numbered list. The thing is that there are often more than one actual lines per list item, like so: 1) ____________________________________ _______________________________________ ____________ 2) ____________________________________ ______________________________________ etc. What I have been trying to figure out is a way to jump to a specific *list number*, rather than actual line number. I have a lis...

How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in another column? Example: I need the total of 5 units sold int the month of January 2006. I have the sale ammounts in one column and the closing dates in another. What forumla would I use for this? Thanks in advance for any help. =SUMPRODUCT(--(MONTH(B2:B50)=1),--(YEAR(B2:B50)=2006),A2:A50) for January change 1 in the month part to a different month -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Alan" <Alan@discus...

Need a simple macro that will import any one of a number of text files in a folder
I'm having problems coming up with a macro that will allow me to import a text file of choice from my default file location that I have set up with Excel which contains several text files. I want the macro to open a "Open" file dialog window where I can then choose the text file of choice. I also need it to open the file as delimited, starting at row 23 and with the tab selected as the only delimiter. Please help... thanks, steve Steve I have been using the following for several years to obtain a spreadsheet from a .csv file downloaded from a bank. You will need to add the...

delete rows containing blank/missing values
I have very large database( almost 500,000 ) that contains road characteristic readings in two columns taken by a highly sophisticated instrument . I would like to remove the rows that the instrument missed to take the reading. Could some one help me in doing this. The normal technique would be to construct a SELECT query that returns only the rows you want. What distinguishes these rows from the others? On 10 Mar 2007 21:47:50 -0800, prasanna.kusam@gmail.com wrote: >I have very large database( almost 500,000 ) that contains road >characteristic readings in two columns taken by a hi...

ow to create comparison chart using text as value data?
lplease help me. I am trying to set up a comparison chart of 3 computers cost and features! I cannot figure out how to use the features as values on the x or y axis says data range is to complex so cant select rows or columns. I am trying to enter the features as my source data too bungled dont know what to do term project is now overdue and I am stressing out!~ Dont want to fail. please help me!do i have to redo the whole chart or what any suggestions and demonstration would be deeply appreciated. thanks, Dee Johnson The first thing you need to do is think how the chart should look. Wh...

How can I count unique values in a query in the report footer
Am using Access 2003 I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the repo...

Extract list using functions
Hi, can anyone help with the following: I want to extract items in a list and show them in another worksheet. An example of the master list would be: Name Dollars Quantity Months Customer1 $ Q 12 Customer2 $ Q 12 Customer3 $ Q 6 I want to be able to extract all those customers which have less than 12months data (as indicated in the month column). I want to be able to do this using functions in another worksheet and list those seperately there. Is there a way to do this? Elijah Hi y...