Data Valadation Question

[JimS]

I'm looking into using data validation for my spreadsheet.

Will data validation allow you to do the following.

Let's say cell A1 must match cell D1. So if in cell A1 you put X, but
in cell D1 you put Y, can you have an error message coming up saying
data doesn't match, or whatever?

 

[T. Valko]

Yes, you can do that but the way you've described it you want D1 to match
A1.

Select cell D1
Goto the menu Data>Validation
Allow: Custom
Formula: =D1=A1
Select the Error Alert tab
Enter the message you want displayed
OK out

 

[ShaneDevenshire]

Hi,

You may also want to consider Conditional Formatting, because it allows you
to alert the user with a cell color change. Sometimes that is also useful.

 

[JimS]

So in other words, if the user puts in the wrong data the cell changes
color? I've never used a formula for conditional formatting. Would
you enter the formula the same way that you would in a cell?

 

[JimS]

Thanks, this will come in handy. One other question. Can you do
this:

User inputs "e" into cell A1. In cell D1 they should put the number
"1". Let's say they put 2 by mistake.

Two questions:

1. How would you write the formula so that they have to put a "1" and
how would you write the formula that makes it their option. In other
words, they should put a "1", but they don't have to if they don't
want to.

If they correctly put in a "1" I don't want any message to show up.

Is that possible?

Thanks for the help.

 

[T. Valko]

Ok, I guess 1 has a direct relationship to "E"...

The formula would be:

=AND(A1="E",D1=1)

Now, to allow the user a choice:

On the Error Alert tab you can set it to different levels of alert. To give
the user a choice to accept the entry set the error alert to Warning from
the Styles drop down list.

If A1=E and if the user enters 1 in D1 no message will appear. If the user
enters anything else then they'll get the warning message with the option of
accepting the entry.

 

[JimS]

Thanks, Biff. I've been playing around with this. It gets tricky,
especially if you want to use a range or column instead of a single
cell.

Do you know of a good turorial, video or otherwise on the web I could
read to try and get myself up to speed on this kind of data
validation?

Thanks again.

 

[T. Valko]

Maybe here:

http://contextures.com/tiptech.html

There's a lot on data validation but it's mostly about setting up various
types of drop down lists.

 

[ShaneDevenshire]

Hi,

Using your original example that A1 must equal D1.

1. Select either cell, which ever one you want to change color, lets say
for this example A1
2. Choose Format, Conditional Formatting, and from the first drop down pick
Formula is, in the second box enter =A1<>D1
3. Click the Format button and on the Patterns tab pick a color.
4. Click OK twice.

When A1<>D1 A1 will change color. If and when they do become equal than the
formatting will automatically disappear. This works for number, text, dates
or even the results of formulas.

If this helps, please click the Yes button.