#### rks Data Validation

```Hi
I am entered 4 digit number in cell A2. i wnts when i entered number in cell
a2 its asked the number which catageory like abc, if a then show number*, if
b thn number show in cell number** otherwise normal number which i type.
witout selection cant go other cell. how is possible, pls help me
RKS

```
 0
Utf
4/5/2010 4:57:01 AM
excel.misc 78881 articles. 5 followers.

1 Replies
575 Views

Similar Articles

[PageSpeed] 20

```I bet you could do something like this, but I wouldn't.

The 4 digit number goes into column A (say A2), the category goes into column B
(B2) and the results (or warning message) goes into column C (C2).

The formula in C2 would be:
=IF(A2="","",IF(B2="","Error:  Please enter a category in column B",
IF(OR(B2={"a","b","c"}),B2,A2)))

You could use Data|Validation (xl2003 menus) to limit the choices in both column
A and column B.

Debra Dalgleish has some notes on data|validation starting here:
http://contextures.com/xlDataVal01.html

And you could use Format|Conditional formatting to make the error message show
up in big, bold, red letters.

Debra has some notes here:
http://contextures.com/xlCondFormat01.html

=======
ps.  If the list of categories gets large, you may be better using another
formula in column C.

Maybe you could create a table somewhere and check the values that should be
used instead of the real number.

RKS wrote:
>
> Hi
> I am entered 4 digit number in cell A2. i wnts when i entered number in cell
> a2 its asked the number which catageory like abc, if a then show number*, if
> b thn number show in cell number** otherwise normal number which i type.
> witout selection cant go other cell. how is possible, pls help me
> RKS

--

Dave Peterson
```
 0
Dave
4/5/2010 11:34:00 AM

Similar Artilces:

Keeping Formulas Not Data
I input my formula, but would like to fill in the data later. How do I save the Formula without there being data. I am trying to create time sheet that will be filled out weekly so the data will change. I want the formulas to add hours etc. to be in the worksheet with or without the data. Help!! You don't need data in order to have formulas. What is the real question here? Do you want the cells with formulas to look blank until data is entered? I suggest you trap for no data. In B1 enter =3DIF(A1=3D"","",A1) will return a blank cell until = d...

Removing data from a series
Hello everyone, Thanks for the help on my last question, it got the job done! I now need to remove data from a series. I have created a series o numbers in a column based on messages in another column, heres a example: C1 C2 C3 1- LOAlarm 1-LOAlarm 2- LOLOAlarm 2-LOLOAlarm 3- LOLOLOAlarm 3-LOLOLOAlarm This goes on for about 2000 messages and was created by the equation C = C1&" " &C2 and now I have to reverse the operation to display onl LOAlarm and su...

Data Save error
GP Ver 6.0. Recently I am facing an error with normal users unable to save records, where as the system administrator is able to save the same information. Please advice on this error, what has to be done. I have disabled the windows user ID of the Database owner. Thinking of this made Dynasa as the DBO. Database is having enough free space available. Hi How big is the company database? Is it over 10GB? Regards James "Matthews" <Matthews@discussions.microsoft.com> wrote in message news:3FF3F553-5B18-4D8A-B65E-8A253BF94BB2@microsoft.com... > GP Ver 6.0. Recently I...

Data Conversion
I took the plunge and purchased Quicken XG 2005. Microsoft hasn't released a deluxe version for the Canadian market in over five years. However, I have found it nigh impossible to convert my MS Money 2000 Deluxe files to Quicken. I tried QIF only to find dates disappearing and having the closing balance for the same account in both programs almost \$6000 different. Last hope is to download a trial version of MS Money that will accept Cdn\$ and terminology (RRSPs, RESPS etc) and convert my old MS Money 2000 to a newer MS version and then convert those files to Quicken, who appear t...

Data Table #2
Hi I'm going around in circles!! I have the following: Tons Price/Ton Sales 555.00 6,420.00 Prod 627.40 5,482.86 Contr 937.14 6,420.00 937.14 1000 937.1380002 1500 937.1380002 2000 937.1380002 2500 937.1380002 3000 937.1380002 3500 937.1380002 4000 937.1380002 4500 937.1380002 5000 937.1380002 5500 937.1380002 6000 937.1380002 6500 937.1380002 7000 937.1380002 7500 937.1380002 8000 937.1380002 8500 937.1380002 9000 937.1380002 The 937.14 in the table to the right of 6420 is a formula which is 6420-5482.86 The 6420 is a cell reference to the 6420 in the table abov...

How do you sort the data by the month of the data?
Hi, The file has date formate as MM/DD/YYYY and I want to keep it that way. Is there anyway the file can be sorted by only MM? I am trying to sort by Jan, Feb, March...... November, December together. Thanks, Saz You should create a new column, let's say G, with the formula G2: =MONTH(A2), where A is the column containing the date. Then you can sort according to this column. But if you have dates for more than one year, all the entries for January 2006 and January 2007 will be sorted together. Use instead G2=YEAR(A2)*100+MONTH(A2) to avoid this situation. Stephane. -- Message posted ...

Data Validation #18
I have created a drop down list by using data validation. The issue is I need to give people the ability to see the description of the item but have the item number populate that cell (which is to be used for other lookups and tables). I have done this before with a description in "a1" and the item number in "a2" but because of the number of cells that have to have the numbers in them, it would create a huge spreadsheet. 1) Can this be done using data validation? If so, how? 2) The cell width is smaller than the description. Is it possible to extend that so tha...

database query not showing foxpro database How I import data
I have install office 2003 I used to access data from a foxpro programm in excel 2000 ealier, but since I have install ms office 2003, excel databases shwoing only debase file , excel file MS Acess database. Its not display Fox-Pro Database to acess I downloaded fox-pro driver and intall fox-pro on my computer but Foxpro database does not show in databases filed. kindly advice what to do? if any special driver rquire please send too "mangat" <mangat@discussions.microsoft.com> wrote in message news:3FD4B949-881F-4407-9B79-23B5BCF318C6@microsoft.com... > I down...

Userform data
I have three userforms, each of them getting their data from three different worksheets. Most of the work I do is in a fourth worksheet where the three userforms should pop up. To load each of these userforms, I used : UserForm1.ListBox1.RowSource = "b7:b" & items_quantity Obviously, this works as long as the active worksheet is that where the data is located. Is there any way of collecting this data referencing the worksheet where it is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" & items_quantity)). I tried that but it doesn't work. ...

rks Data Validation
Hi I am entered 4 digit number in cell A2. i wnts when i entered number in cell a2 its asked the number which catageory like abc, if a then show number*, if b thn number show in cell number** otherwise normal number which i type. witout selection cant go other cell. how is possible, pls help me RKS I bet you could do something like this, but I wouldn't. Instead, I'd use multiple columns. The 4 digit number goes into column A (say A2), the category goes into column B (B2) and the results (or warning message) goes into column C (C2). The formula in C2 would be: =IF...

The query could not be processed: Error opening data file
Using Excel 2003. Create a chart and have saved it as a web page and published to a remote share. When I save it locally, it works fine and is interactive,etc.. When I save it remotely and open the htm file in a web browser, it opens like the charting area but the data is not present. Gives error below in the pivot table that appears. Getting error The query could not be processed: o Error opening data file "file://\\remoteserver01\hassan\numbers_files\Numbers_Jan24_12558_cachedata001.xml". Any idea how to fix it ? I want others to access it remotely. Thanks Trying other...

Controlling data in transaction details
I'd like to be able to suppress or change the S/N: that shows up in the Store Operations POS transaction grid and then prints on the receipt. Having it on screen isn't so bad but I really need it off the receipt. But the receipt just prints transaction.description so I guess I need to intercept it earlier in the process. Can I? -- Greg Staples Automated Options, Inc. Spokane, WA Greg, You could add a ~ to the Description column header alignment (change &lt; to &lt;~) in the TransactionDetails part of the receipt. This will truncate the Description and only print o...

Select data from a list with different colors
Ive been racking my brains out on this problem, but cant' find a solution.. I have a list of 16 items (row a50 to a66), each line is a different text value, next to that list, is another list in b50 to b66, those are formatted in different colors. what I need is when a user selects a value from the drop down list, the color of the selection corresponds to what is selected.. for example 1 blue 2 orange 3 pink and so on.. the drop down list cell is in a1, when the user selects a item in the list in cell a1, the color specified should be changed in that cell, if they select 2 then the c...

How to use out of bound data?
I used CAsyncSocket to send out of bound data. But the data only can be received by original data method. Has any one used this class to send and receive out of bound data? That's out of BAND data. Out of bound data means you have a memory overwrite. According to the documentation, this should be received by the OnOutOfBandData handler. joe On Fri, 26 May 2006 13:14:28 +0800, "fadics" <fadics@sohu.com> wrote: >I used CAsyncSocket to send out of bound data. But the data only can be >received by original data method. Has any one used this class to send and &g...

Extracting Data
Can money data be extracted from software like Crystal Reports? Sadly, no. See http://umpmfaq.info/faqdb.php?q=10 and http://umpmfaq.info/faqdb.php?q=9. "Star" <Star@discussions.microsoft.com> wrote in message news:2B234AD9-22CF-465C-BA11-3B24608D22E9@microsoft.com... > Can money data be extracted from software like Crystal Reports? ...

Set IE's favorite data... how?
Hello~ I got all my IE's favorites data by GetPrivateProfileString( "InternetShortcut", "URL", "nothing"...); Hmm.. That's ok. So, that I wonder is how to set favorites(bookmark) data... reverse work in GetPrivate...() If I make the *.url files and calls SetPrivateProfileString("InternetShortcut", "URL", "nothing"...);, is it works? Could you give me some advice? And I don't know how to get Netscape's bookmark. As I know, Netscape reposit bookmarks data in bookmarks.htm. But the folder that htm file is in, looks no...

data series in rows or columns.
I am having problems in a class here which the teacher can't seem to help with. I was making a bar graph, and the A column had East, West, North, and South. The one row had 1st Qtr, 2nd Qtr, 3rd Qtr, 4th Qtr. The bars represent the data. In Chart Wizard, Step 2 of 4, chart source data, there are two check boxes marked, "series in:" and the boxes are marked "rows" and "columns". Here's where I get lost. Which way do you read the data? Hi Mike "rows" uses the headings across the row as the labels for the X axis and graphs the data acc...

drop down data?
I'am trying to set a multiple shift weekly work schedule for 55 employees.I thought drop downs for each employee and for each day, with the drop downs containg all shift times would work the easiest to quickly set up schedule. Having trouble setting up, need step by step if possible scheduler, have a look here http://www.contextures.com/xlDataVal01.html -- 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 "scheduler" <s...

Import ing Data
I have about 5 or 6 stocks I watch and an older Stock Program [Supercharts 4.0 Built 10]. I would like to import the data for these companies from Yahoo, but after the download into Excel the data is ascending rather than descending order. Would anyone know how I could enter these figures into this program? Thanks, Carl Hi Carl - Is the only problem that the data is sorted wrong? You can very easily sort it in Excel, then dump it into Supercharts. Or is there an issue getting any data into Supercharts? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http:...

Unable to Update free/busy data
Why do some of my users get this message and some don't? What does this message mean? How do I fix this? -- Wack ---- This message will self destruct. Dave: There are a few causes for this. Try this first: run outlook.exe /cleanfreebusy on the user's PC, or on a PC with an Outlook profile set up for the user. See http://support.microsoft.com/default.aspx?scid=kb;en-us;813609 also. Regards, Martin, MCSA: M "Dave" <dav5489@hotmail.com> wrote in message news:OZILZSOyEHA.2036@TK2MSFTNGP12.phx.gbl... > Why do some of my users get this message and some don...

sum data betwen two blank row
I have a file like below sub-total jan 10 20 30 <== how to creat a formula to sum data between blank row ? feb 30 10 10 50 Assuming your data starts on row 2, allowing for headers, put this in C2: =3DIF(AND(B3=3D"",B2<>""),SUM(B\$2:B2)-SUM(C\$1:C1),"") and copy down as far as needed. Hope this helps. Pete On Nov 27, 7:10=A0am, eva cheng <eva ch...@discussions.microsoft.com> wrote:...

Dynamic Charting Question re data
Hello: I have set-up a chart that utilizes the techniques described in previous postings regarding Dynamic Charting within Excel (using the non-VBA technique of data labels utilizing the Offset formula). In a prior post a list member described the use of the NA() formula as part of an IF() statement to eliminate non-blank data from appearing on the chart if no data was present. However I am now left with one issue that I need to resolve to make this chart fully usable - being that my X-axis label shows the future periods with no data (in fact I used the same formula and end up with #NA show...

centering data on pages
Im making a 2004 calendar in excel, and ive set it in the print option to center the cell both horizonally & vertically. Each month of the calander starts in a new set of the print boarders so that each prints on a seperate page, but when i do print preview excel only centers the last page vertically, the rest of them are all at the top of the page. Does anyone know how i can fix this You have probably got a few lines left blank at the bottom of each page. Excel interprets this space as used, because it is not the end of the spreadsheet. If those lines were not blank, you would expec...

using a character value for the data in a pivot table
I want to create a pivot table with a string value in the data portion. In my case, I am presenting test results in the spreadsheet. The row headers are the student name and test date/time. The column headers are the questions in the test. The data should display the character response to the question referenced in the column header. For example, a test might have the question "where do you live" (a column header), and the answer is "USA" (the data). The spreadsheet contains four columns: Name, TestDate, Question, Answer I want to create a pivot table with the stud...

DCount, with mixed type data
First off, here is a sample set... -------------------------- Database: MyCode Score Rating 9 641 22 G 649 23 9 675 23 J 649 23 M 999 95 B 529 21 -------------------------- and my Criteria: Rating MyCode 22 a 23 b c -------------------------- I can get a DCOUNT as long as I use a letter for the criteria under MyCode. What I need to do is get a DCOUNT of everything except the 9's (<>9) in the criteria. BUT, everytime i put the <>9 in, I get bad results. How do I count all the non numeric entries?!?! Use DCOUNTA instead ( or post your formula that does not work if you use ...