Validating Data from another source

[paulnray]

Hi All,

I'm using Excel 2003 had a look around the forum and couldn't find an answer to this so hoping that some brilliant person can point me in th right direction.

<b>The problem</b>

Each month I have around 200 different records that have been entered into a database by different people but as free text. for simple reason we will call it two columns Column A = Type Column B = Reference

I would like to validate that depending on what has been entered in column A that the format in column B is right an example is if we say Type is 1 then the format for Reference is NNLNNNNL (where N is number and L is letter) if it's type 2 then the format needs to be LLNNNNN so i have different lengths of reference. {i it could be done for type 1 then that would be a major start as this is the bulk.

<b>What would be nice</b> is if the valadition is wrong the reference cell is coloured RED and wrong is entered in Column C

<B>As it's Xmas what would be really nice</B>
The error line copied to a error worksheet and a column that has a hyperlink back to the main spreadsheet as the dataset is a lot larger.

If anyone is able to help it would really be appreciated.

Kind regards

Paul

 

[General Ledger]

Re: Valadating Data from another source

paulnray,

It sounds like at least part of the solution is using Conditional Formatting. With this, you can control the formatting (color) of cells based on criteria you set up. Are you familar with this feature (which is greatly enhanced in Excel 2007)?

General Ledger

 

[paulnray]

Re: Valadating Data from another source

Hi GL,

Thanks for the reply. I'm aware of basic conditional formating but the problem I have is that my data could be in different formts for example

Type 1 - 12C34567O would be right but
Type 1 - 12C345670 would be wrong also
Type 2 - PH12345 would be right

As I said before all help appreciated.

Paul

 

[paulnray]

Re: Valadating Data from another source

I posted this last night but it has quickly moved off a number of pages just wonder if anyone can give me any suggestions.

Thanks

Paul

 

[General Ledger]

Re: Valadating Data from another source

paulnray

I am finally getting back to your problem. I don't have a 100% solution but here is some direction:

Assume cell A1 has the Type (1 or 2) and cell B1 has the Reference.

Go to the cell in which you want to apply conditional formatting (probably B1). In Excel 2003, go to the menu Format > Conditional Formatting

In Condition 1, select from the drop down box "Formula Is", and not "Cell Value Is".

In the field to the right enter this formula

=OR(AND(A1="1",LEN(B1)<>8),AND(A1="2",LEN(B1)<>7))

This formula tests the Type in cell A1 and the length of the entry in cell B1. The result is either a TRUE or FALSE.

Select the Format button in the bottom right.

Pick the Font, Border and Pattern (you prefer red), to be applied when the result of the formula is TRUE My personal favorite is to make the Pattern black and the Font color white and bold. Then when you print on a black and white printer, you still get these cells noticed.

Select OK

Select OK again

You can copy the Conditional Formatting to other cells by selecting the Format Painter (the yellow paint brush icon) and touching the cells to where you want the formatting applied.

This is just a start. I need to add criteria for if each character in the Reference is a letter or number as desired. Unfortunalely, I don't know an easy way to do this. You might have to test each position like: Left(B1,1)<=9, Mid(B1,2,1)<=9, Right(B1,1)>=A, etc.

As for the Xmas request, I am sure that would require a Macro. That is most certainly beyond my skills.

Happy Holidays,

GL