Comparing values in non contigious cells

[thePriest]

What I am trying to do is write a validation routine to check the
values on an Excel worksheet.

For instance, cells A1, A3 and A5 will contain different strings
depaending on the data entered by the user. This is being done via the
Excel data validation tool.

A1 = Leicester
A3 = Leicester
A5 = Coalville

Once the user has completed the form, he/she will click an update
button to copy the revisions to a new worksheet (this I have no problem
with). Before the data is copied however, I want the routine to check
that the contents of the three cells meet the specified criteria, in
this case "Leicester", "Leicester" and "Leicester", the above example
will obviously fail and request that the user make the appropriate
adjustments (again, this I have no problem with).

Any help with the validation would be much appreciated.

 

[Guest]

You need to compare A1 to A3 AND A3 to A5: try this formula in a cell:

=(A1=A3) *(A3=A5)

In code:

Valid=(range("A1")=Range("A3")) AND (Range("A3") = Range("A5"))

By default, the comparison is case insensitive.

 

[thePriest]

Not sure this is quite what I'm looking for but I could be weel wrong.

I need to tell Excel that unless all three cells meet a certain
criteria, then the validation must fail. The problem is that the
single entries for each cell will be different but mean the same thing.

A1 = East Midlands Housing
A3 = Allocations EMHA
A5 = Arrears EMHA

In this case, the validation would pass because all the entries relate
to the same company. I need to build this into an array (somehow) and
compare the cell values with that array?

 

[Tim Williams]

You'd need to create (eg) a list in a hidden sheet which defines which
terms are equivalent (do you know all possible values which could
appear here?)

eg:

1 East Midlands Housing
1 Allocations EMHA
1 Arrears EMHA
2 West Midland Housing
2 Allocations WMHA
.....etc

You could then use this list in your validation routine using Find()
and comparing the "index" for each of the three items.
Other than this (having a list of matching entries) it's not easy to
see how else this could be done, unless there is some constant pattern
which relates the three items.

Tim