Jack, For cell A1, use Data / Validation, Allow - Custom, and in the formula area, enter (take out any extra line returns before using this): =OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),NOT(ISERROR( VALUE(LEFT(A1,LEN(A1)-1))))),AND(NOT(ISERROR(FIND(LEFT(A1,1),"KD"))),NOT(ISE RROR(VALUE(RIGHT(A1,LEN(A1)-1)))))) HTH, Bernie MS Excel MVP "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl... > Hi all > > I want to set data validation on a cell so that it will accept the following > (and only the following) text strings: > (1) "BR" (literally) > or > (2) "NT" (literally) > or > (3) "nA" where n is any positive integral numerical value including zero and > A may take any of the values "L", "P", "T", "V" or "Y" > or > (4) "An" where n is any positive integral numerical value including zero and > A may take either of the values "K" or "D". > > Is this possible, please, and if so how? thanks > > -- > Return email address is not as DEEP as it appears > >

0 |

5/20/2005 1:11:27 PM

Actually, I forgot about the positive integral part. Change the Data validation formula to =B1 and in B1, enter the formula =OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT (A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1)) ),FALSE))) You need to use cell B1 because the formula is longer than the data validation formula length limit. HTH, Bernie MS Excel MVP "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl... > Hi all > > I want to set data validation on a cell so that it will accept the following > (and only the following) text strings: > (1) "BR" (literally) > or > (2) "NT" (literally) > or > (3) "nA" where n is any positive integral numerical value including zero and > A may take any of the values "L", "P", "T", "V" or "Y" > or > (4) "An" where n is any positive integral numerical value including zero and > A may take either of the values "K" or "D". > > Is this possible, please, and if so how? thanks > > -- > Return email address is not as DEEP as it appears > >

0 |

5/20/2005 1:19:56 PM

Jack, You would need to use the worksheet change event - VBA code, so you would need to be able to have code, and have macros enabled (some folks don't like that.) Besides, it really doesn't add to the processing overhead. Excel only calc's the cells that are directly affected by a change. You could have 1000 cells, and change 1 of those, and only the corresponding cell in column B will be calc'd. Using VBA will actually slow your file down more. HTH, Bernie MS Excel MVP "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message news:O8v3fXUXFHA.3620@TK2MSFTNGP09.phx.gbl... > Just one minor problem: > As the formula in B1 refers to the address of the cell to which the data > validation applies, it seems that I am going to have to copy this formula > down to each row in column B for which a corresponding entry is to be made > in column A. I would have preferred as solution that just requires the > formula to be stored once, simply to reduce the processing overhead on the > workbook. I can live with it as it stands, but is there a way around that? > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message > news:%238Ncf6TXFHA.2288@TK2MSFTNGP14.phx.gbl... > > Actually, I forgot about the positive integral part. > > > > Change the Data validation formula to > > > > =B1 > > > > and in B1, enter the formula > > > > > =OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR > > > OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF > > > T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE > > > FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT > > > (A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1)) > > ),FALSE))) > > > > You need to use cell B1 because the formula is longer than the data > > validation formula length limit. > > > > HTH, > > Bernie > > MS Excel MVP > > > > > > "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message > > news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl... > > > Hi all > > > > > > I want to set data validation on a cell so that it will accept the > > following > > > (and only the following) text strings: > > > (1) "BR" (literally) > > > or > > > (2) "NT" (literally) > > > or > > > (3) "nA" where n is any positive integral numerical value including zero > > and > > > A may take any of the values "L", "P", "T", "V" or "Y" > > > or > > > (4) "An" where n is any positive integral numerical value including zero > > and > > > A may take either of the values "K" or "D". > > > > > > Is this possible, please, and if so how? thanks > > > > > > -- > > > Return email address is not as DEEP as it appears > > > > > > > > > > > >

0 |

5/20/2005 2:33:02 PM

Hi Expert, I need your help on how to count data but to ignore duplicates. Below count results should be = 3 (ignore duplicates) Inv No. 9123 9123 9125 9128 9128 Thanks for your support, Try =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")) -- Jacob (MVP - Excel) "Rechie" wrote: > Hi Expert, I need your help on how to count data but to ignore duplicates. > > Below count results should be = 3 (ignore duplicates) > Inv No. > 9123 > 9123 > 9125 > 9128 > 9128 > > Thanks for your s...

In purchase orders, our customers need an option to use the current cost typed into the cost column when performing calculations with the formula function. This is because many of our customers need to reduce the value of the PO by, for example, 5% because they have met payment terms that give them a discount. The simplest way to achieve this is by using the current cost that has been typed into the cost column. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree&q...

Hi I have one sheet like: 0123 Food 0124 Drinks ..... ...... where I store product informations in another sheet I have pivot table where I have information about the price: 0124 45$ 0123 3$ ..... .. How can I make excell to look in second sheet for particular value and retrive the price in first sheet ? Please help me with this as I have no idea Hi use VLOOKUP. See: http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "Piotr" <hokah@wp.pl> schrieb im Newsbeitrag news:cq7be2$jb9$1@inews.gazeta.pl... > Hi I have one sheet like...

Hi, I've got two spreadsheets. One has got data on, and one is blank but with validation on (from the data -> validation menu). I need to get the data onto the sheet with the validation and got through the validation process. If you paste or use vb commands such as range or cell, the validation is bypassed. Is there a way of simulating the act of typing in the data into the spreadsheet? Unfortunately I don't have control from either the source sheet, or the destination, but I can set up a routine to transfer the data. Thank you Ian ...

Hi- I'm using Excel 2002 and I've got a lot of XY (Scatter) graphs. When I add a new data series to one graph, it is defined by 3 values (Name, X Value and Y Value) If I want to add this data series to 7 or 8 add'l graphs, I know that I can copy/paste these 3 values to each of them. Is there a simpler way to accomplish this? thnx ...

I am trying to add data from another sheet to the data present in this sheet. I dont know what the formula has to be or how to start. I have 2 sheets, "today" and "till date". "today" will be updated on a daily basis. I want a formula where on "till date" sheet, today's data is added to the previous days dat and show son "till date".... Plz help me... How about using a built in form? Select the complete data with headings on 'Till Data' Sheet Press "Alt+D" and then O. Simply add records which will update your...

I cannot seem to get this correct. I am trying to run a query on two databases one of which is linked to the other. I am trying to get the query to display a 0 for the total defects counted from one table based on the date and time from another table. So far when I run the query I get only the counts from the values that are not null. Here is the SQL Statement: SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift, Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1, Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL, IIf(IsNull([Counto...

In pub 2003 I go to tools > options and set file locations for publications and for pictures. Pub 2007 does not seem to work the sane way. How can I tell pub where to look for files and where to save them? -- SOB711 Weird ain't it? I think they forgot to put it in. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "SOB711" <SOB711@discussions.microsoft.com> wrote in message news:86B625A5-8F37-48BE-A4D8-880ACA021A64@microsoft.com... > In pub 2003 I go to tools > options and set file locations for > publications > and for pictures. >...

Hi! I have been bilding a form for orders (frmOrders) including a subform for order detais (frmRows). In the frmRows is a control for UnitPrice. I should be able to enter data into it in 3 ways: 1. Directly from table Products. 2. Based on calculation square meter*m2Price. 3. Manually. Placing a sentence =IIf(m2Price>0;m2Price;ProdPrice) into the UnitPrice control works in cases 1 and 2 but entering data manually does not. Now I have solved this problem by macro: doubleClicking on control UnitPrice copies the value from m2Price and I can edit the UnitPrice control. I know there must be m...

I have large list from our fax servers that produces a lof with : Start of FAX Connect date/time, End of Fax Connect date/time I know I have 24 lines but need to find how many lines have I bee using historicaly to do capacity planning. Any suggestions? M -- Message posted from http://www.ExcelForum.com ...

Hello All, I need some assistance. Outlook 2003 When I go to compose new email you should be able to click on insert and see where is shows picture and it should have a right arrow and when you mouse over it a drop down menu should be there and one of the options should be Clip Art. At this time there is the option to choose picture but thats it no arrow with a drop down menue with any other options. I dont know where it went or what could have happened to make it not available. I have all recent updates for Office 2003 installed. Thanks, Chris Are you still using Word as your email ed...

To you XML validation experts: I have a schema that validates an XML file, but there are a couple of other types of validation I'd like to do on it. They are basically string fields that must be validated against a set of possible codes, but there could be tens of thousands of possible codes. Naturally, these codes are in a table in SQL (actually there are a few code types, but the problem is the same for each). In the past I have always done this as an additional code step AFTER schema validation, but I am being asked whether it could be "plugged into" the schema validation...

Hello, In a field there are entries that have the symbol * entered for some reason. For example: DBW*R2 3*FLEW QQ*IF94 How can I filter and find all the data in that field that has "*" entered. I can't seem to find the filter for it. Thanks Dimitris On Wed, 3 Feb 2010 14:29:28 +0200, "Dimitris" <ipackREMOVE@otenet.gr> wrote: Use this expression: Like "*[*]*" This means a wildcard followed by an asterisk followed by a wildcard. -Tom. Microsoft Access MVP >Hello, > >In a field there are entries that have ...

I have just installed outlook 2003 + bcm. I copy the .pst file from to my notebook on a daily basis. I discovered that the bcm data are not copied together with the .pst file. I have tried to find the location of the bcm data file but it is nowhere to find. Can someone give me a clue? Frank Simons ...

Help - I have several spreadsheet with military time i.e. 1701, 1615, etc. The cells are in general format. Since I need to calculate hours from these times, I tried to convert them to [h]:mm but when I do, it changed the number 1701 to 40824:00. I tried to change the cell format to hh:mm but it gave me zeros. I use Excel 2000. Thanks. Hi With your number in cell A2: =TIME(LEFT(A1,2),RIGHT(A1,2),0) -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "klaire" <anonymous@discussions.microsoft.com> wrote in message news:09db01c4a620$e775390...

I am having serious trouble with my excel system! I have 1 column for time, another for temperature, I need time to be on the y-axis and Temp as a line on the graph. Damn auto-grapher creates a graph with both time and temp as lines in the graph. PLZ HELP!!!11 P.S Hurry and answer this i have only today to do this!! Hi, If you add a column header to your Time column you will help excel to guess your data layout. It expects the first column to be Category or X values. The second column to be Y Values. <blank> Time 3 00:00 5 01:00 6 02:00 9 03:00 By the way ...

I'm trying to create a cluster column that has a label above each data point. The columns are the total number of an event that happened and then I need a label that shows what % of a goal those number of events represent. There is no relationship between the data and the % label. Jan (cluster 1) A: 34 label: 98% B: 52 label: 102% C: 23 label: 96% Feb (cluster 2) A: 36 label: 102% B: 56 label 110% C: 21 label 92% Can someone help me design this chart? Thanks. John Try one of these utilities which add labels from a worksheet range to a data series. Both are free, e...

I'm getting this error while trying to store data. I'm using an odbc recordset. The problem is that I've got around 400 fields involved in the insert statement (main table, plus detail tables). My question is this: Is there any way to find out which field(s) are causing this error? This is a cgi "script" on a server, so I can't debug with a debugger. (makes the whole situation a little tougher) Maybe the string size is too small to hold the total sql-command? "Cliff" <cliff.newton@gmail.com> schrieb im Newsbeitrag news:1187195184.051633.264680@22...

Dear all, I'm missing a tool to maintain local data groups for new users. It's not feasible to manually generate, configure 20+ new datagroups for each new user!!! what can you recommend? will there be a tool from microsoft (CRM 4.0 :-( )? TIA Stefan ...

Hi, I would like to use data as input for Linest that is in the following cells: known y: A1 to A3, A10 to A12 known x: B1 to B3, B10 to B12 I have the feeling that this is almost answered in the following posts, but I do not understand it: http://groups.google.com/group/microsoft.public.excel/browse_thread/t... Regards, David ...

Hello, I am having a couple of issues with Excel 2007 and being able to retrieve data into Excel 2007. The first issue is that I cannot see a System DSN that was added through the 64 bit ODBC Administrator screen on Windows 7 in Excel 2007. What I do is add the System DSN and when I open Excel 2007 and then click on the Data tab and then click on From Other Sources then click on From Microsoft Query the Data Source I added is not in the list. So since the System DSN did not show up I removed it and added a User DSN and did the same steps as above and when I open the table I ...

Without necessarily deleting the chart and then recreate a whole new chart, how can I get the data points to revert back to being in the center of all columns rather than equally spaced starting from the left axis to the right axis? This issue was brought about by way of using the Area method for one of the data series, but went attempting to revert back, it doesn't go back to the original line data format as described above? It goes back to the line, but the data points are not at the same places. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 Hi, Try ...

I found article ID 213983, http://support.microsoft.com/default.aspx?scid=kb;en-us;213983, that finally answers my question why I sometimes can't open files. This has been a great mystery to me because I can open the files if I copy them to my local hard drive. Unfortunately, for a lot of reasons, I can't rename the directories or files. Is there any way to get around the 218 character limit? Is this limit carried in later Excel versions? ...

All of a sudden, I add sales numbers to a row that was included in the summation. The sum function did not sum the newly inserted information until I double-clicked on the formula cell and re-entered. This now holds true for all of my linked worksheets and formulas, rendering me useless for entering additional data. Anybody know why this is happening and how to remedy? Hi you probably have disabled the automatic calculation. Goto 'Tools - Options - Calculation' and check 'Automatic calculation -- Regards Frank Kabel Frankfurt, Germany Oyitch wrote: > All of a sudden,...

I have a template file that I use as a mailer. Each week I set up my merge fields and merge from an excel data file. The following week I want everything to look exactly the same but want to merge from a different excel list. I open the publication, tell it to proceed without connection to the datasource (my old list), choose tools, mail merge, use an existing list ... and all of my perfectly placed merge fields disappear! So every time I want to use another list I have to put my merge fields in again. My headers are the same in every excel file so mapping should not be a problem. ...