Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in non contigious cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in non contigious cells
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in non contigious cells
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in non contigious cells
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 "thePriest" wrote in message oups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing the values in two cells, then counting how many differ | Excel Worksheet Functions | |||
Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
Comparing values between columns only when there are values in bot | Excel Worksheet Functions | |||
Multiply non-contigious arrays | Excel Discussion (Misc queries) | |||
Help: Comparing two values | Excel Programming |