Multiple data validation criteria?

Working in 2003, but this workbook has to also work in 2007

For this project I have to avoid macros because users are remote and may not 
enable macros.

I have a column which needs two different data validation criteria;

(1) ColumnA >Column B and ColumnA <Column C (I can do this with data 
validation)

(2) ColumnA does not contain values at more than one decimal place
   4.2 is ok, 4.21 is not
  I can also do this with data validation (custom formula)

However, Excel appears limited in that I cannot apply more than one data 
validation criteria. Is there any (non-VBA) workaround, or am hosed? I'm 
considering the use of conditional formatting, but again this only provides a 
visual prompt, and does not force the value to meet validation standards, and 
unlike the data validation, there is no prompt to tell the user why the value 
is inappropriate.

I tried setting up a mirror cell (D1=A1) and set the secondary data 
validation criteria on D1, but changes in values passed via formula do not 
appear to trigger the data validation check.

Thank you for any suggestions,
Keith
0
Utf
2/18/2010 12:33:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
3662 Views

Similar Articles

[PageSpeed] 42

Hi,

using custom validation, you should be able to combine these criteria.
You say you already have a custom validation for (2), then just combine
like

=and(a1>b1,a1<c1,<your other custom formula>)

cheers



ker_01;646662 Wrote: 
> 
Working in 2003, but this workbook has to also work in 2007
> 
> For this project I have to avoid macros because users are remote and
may not
> enable macros.
> 
> I have a column which needs two different data validation criteria;
> 
> (1) ColumnA >Column B and ColumnA <Column C (I can do this with data
> validation)
> 
> (2) ColumnA does not contain values at more than one decimal place
> 4.2 is ok, 4.21 is not
> I can also do this with data validation (custom formula)
> 
> However, Excel appears limited in that I cannot apply more than one
data
> validation criteria. Is there any (non-VBA) workaround, or am hosed?
I'm
> considering the use of conditional formatting, but again this only
provides a
> visual prompt, and does not force the value to meet validation
standards, and
> unlike the data validation, there is no prompt to tell the user why the
value
> is inappropriate.
> 
> I tried setting up a mirror cell (D1=A1) and set the secondary data
> validation criteria on D1, but changes in values passed via formula do
not
> appear to trigger the data validation check.
> 
> Thank you for any suggestions,
> Keith


-- 
teylyn

Teylyn  --  'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180256

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
teylyn
2/18/2010 2:55:24 AM
Thank you, that is probably what I'll end up doing. I had hoped for multiple 
seperate criteria so I could take advantage of the data validation error 
message (to provide a separate message for each type of validation), but it 
doesn't appear that Excel was designed for that.
Best,
Keith

"teylyn" wrote:

> 
> Hi,
> 
> using custom validation, you should be able to combine these criteria.
> You say you already have a custom validation for (2), then just combine
> like
> 
> =and(a1>b1,a1<c1,<your other custom formula>)
> 
> cheers
> 
> 
> 
ker_01;646662 Wrote: 
> > 
> Working in 2003, but this workbook has to also work in 2007
> > 
> > For this project I have to avoid macros because users are remote and
> may not
> > enable macros.
> > 
> > I have a column which needs two different data validation criteria;
> > 
> > (1) ColumnA >Column B and ColumnA <Column C (I can do this with data
> > validation)
> > 
> > (2) ColumnA does not contain values at more than one decimal place
> > 4.2 is ok, 4.21 is not
> > I can also do this with data validation (custom formula)
> > 
> > However, Excel appears limited in that I cannot apply more than one
> data
> > validation criteria. Is there any (non-VBA) workaround, or am hosed?
> I'm
> > considering the use of conditional formatting, but again this only
> provides a
> > visual prompt, and does not force the value to meet validation
> standards, and
> > unlike the data validation, there is no prompt to tell the user why the
> value
> > is inappropriate.
> > 
> > I tried setting up a mirror cell (D1=A1) and set the secondary data
> > validation criteria on D1, but changes in values passed via formula do
> not
> > appear to trigger the data validation check.
> > 
> > Thank you for any suggestions,
> > Keith
> 
> 
> -- 
> teylyn
> 
> Teylyn  --  'teylyn.posterous.com' (http://teylyn.posterous.com)
> ------------------------------------------------------------------------
> teylyn's Profile: 983
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180256
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 
0
Utf
2/18/2010 3:42:01 PM
Reply:

Similar Artilces:

WM_QUERYENDSESSION and saving data through a worker thread
Hello I have an application that uses a worker thread to save/load data. I'm wondering what is the best reaction to WM_QUERYENDSESSION in my case. I have to possible scenarios: 1. When WM_QUERYENDSESSION comes fire the thread and wait for it to end. Only then return TRUE from WM_QUERYENDSESSION handler. The problem is that I will get nusty dialog that my application "is not responding". 2. When WM_QUERYENDSESSION comes fire the thread and return FALSE from the handler. When thread is done force application to end. But this way I will probably prevent Windows from closing,...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

multiple CD's, same bank
I've got multiple CD's opened up at the same bank. They are all under the same account number, but each under a separate heading. Each of the CD's mature at a different time, and they will be rolled over into new CD's over the next 18 years. Futhermore, my bank provides online banking services directly within MS Money, including automated download of statements. So...what would be the best way to set up these accounts in Money? Should I create one account named CD and then make a cash transaction for each CD? Should I set up each CD as an individual investment accou...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Downloading multiple messages
All of a sudden outlook has started downloading multiple messages from my pop account (ie 22 of the same) It is also sending out 22 of the same message. How can I resolve this issue? What version of Outlook do you have? Does it download duplicates of *all* messages, or just of one or two messages? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "GW" <anonymous@discussions.microsoft.com> wrote in message news:005101c3bf4c$e748bf30$a501280a@phx.gbl... > All of a sudden outlook has started do...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

The user authentication passed to the platform is not valid 01-10-07
Hi all, In our MSCRM 3.0 when we try and copy a contract that's assigned to a certain user we get the following error "The user authentication passed to the platform is not valid." Even with system admin privileges we cannot copy a contract assigned to that user. If we re-assign the contract to any other user we are able to copy the contract. Anyone got some information to help me understand this better? Cheers Dan Hi All, I know my reply/post is irrelevant for this thread. But i cannot reply to older posts and this is the latest thread in this newsgroup for the mention...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Multiple Sales Transaction for Same Work Order
I am in the process of trying to create a store level report by department, catagory, and item of the moneys received in total by both deposit on work orders and on direct sales that are not work orders. We require a 100% deposit (of a single tender type) on work orders. I then need to break this deposit down by department, catagory and item. Then I must add that to the same break down for direct sales. Thereby getting a total of all moneys received during a specified period for the store across all registers. So basically I am trying to figure out the data structure and org...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

Add data to cell w/o loosing initial data
I would like to know if there is a way to add data to data without retyping. For example I have a colum of 18015555555 and I want to add [rfax:(cell #)@/fn=(phone number)] So I would like to add the brackets - copy from a cell - @/fn= and not loose the data already in the spread sheet. Example 2. Add [rfax:company name@fn/=(saved data here) then close bracket. So I want to add data to cells without loosing the data already in the cells. I have about 600 of them to do and I really don't want to do each one by hand. Please let me know if anyone knows how to accomplish this. Tha...

Removing filters from data
Hi. I have recorded a macro to remove filters from data lasts in advance of performing other actions. However if the data is unfiltered the macro falls over with the message Run time error '1004' ShowAllData method of Worksheet class failed. I think I need some sort of if error continue code or something to check filtering first. I would be grateful if someone could point me in the right direction please. Hi Philip Try If Activesheet.Filtermode Then ActiveSheet.ShowAllData -- Jacob "Philip J Smith" wrote: > Hi. > > I have re...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

Looking up and matching data
I have two sets of data with the same information but not in the same order and am trying to match the data. In each data set I have 10 pools containing 100 loans. Each pool has a unique ID and each loan within the applicable pool has an ID of 1 to 100. I need to look up the Pool ID, then look up the loan ID so that I can extract the property type information from a third column. The Pool ID and property type is text but the loan ID is a number. I am struggling to put together the right combination of formulas to give the property type for each loan within each pool. Any suggestion...

invisible listbox data
I have developed software in Acces XP that is distributed to two different locations and I have noticed some odd behaviour at one of the locations. Sometimes the data in listboxes or combo boxes is invisible. The data IS PRESENT because you can select and use the records as before (although you can't see which ones you're selecting). The combo boxes are poplulated and have the correct dropdown length for the records one would expect. The listboxes have scroll bars where one would expect a scrollbar and multiselcetion is possible where apropriate. I have played around with th...

create a letter with the data fron work sheet
i have a data sheet for the order.like people order staff lik tea,coffee...etc,i ve data including date,name,department,.....etc. when i put the data i want a letter format which picks up the data fro the sheet then i can send it to the person who made the order as confirmatino letter. like it the letter starts dear "......"(it could pick the name fro c1) also how can i create this letter? does it have to be in excel ?? help pleaseeeeeeeeeeeeeeeeeeee!!!!!!!!!!:( :( :( : -- Message posted from http://www.ExcelForum.com As answered in microsoft.public.excel and microsoft.public....

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...

Sending multiple Emails so each person does not get the list
We want to send out multiple emails to several email accounts. We dont want the accounts to see the list of accounts that the email went to. How does one do this in Outlook? In any contacts folder, Tools | Mail Merge is the best choice. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "tom" <Spamblocker@ameritech.net> wrote in message = ...

in Publisher I want to merge/send email with multiple attachments
Using Publisher 2007 I know how to do a merge and then email out from a list. Publisher gives the option to attach multiple attachments to the email but when the send actually goes out it only carries the first of the attachments and discards the others. Is there a trick to making multiple attachments stay attached? or is this a bug... Thanks to anyone who can clear this up.. Rust Gilbert Rust, Since you already know how to do email merges in Publisher 2007, you're probably the ideal person to answer my question...I have been trying to do an email merge using an existing publis...