Data inputted accuracy check

[MJKelly]

How do I run through a few worksheets of data to check that certain
values are correct, according to another criteria?

i.e. if A1 = "Fruit", the value of B1 is either Apple or Pair or
Banana. Or, if A1 = "Vegetable", the value of B1 must be either
Carrot or Potato.
Then proceed to A2 and check the value of B2 and so on.

If an error is found, I would like the cell backgroud colour changed
to Yellow and an error count increased by 1.

I have quite a few categories and values which can be attributed to
them (loads more Fruit and Veg types as well as other food types and
their products), so would I need to contain all of the correct options
in the macro or could it reference and check against a table on a
worksheet?

kind regards,
Matt

 

[glenton]

Hi

The question as you ask it is relatively easily done. My question first
though, is why not just use a vlookup?

You'd create a second table with all the food stuffs (banana, etc) in the
1st col, and the categories (fruit etc) in the second. This is probably a
much shorter table then the large one you're wanting to check.

Then create a third column in the large table with the vlookup formula. If
you wanted to you could have conditional formating to create the colour
effect you're after.

Suppose the short table is in columns A:B of sheet2, and the big database
you're wanting to check is in A2:B1000.
Then in C2 put this formula: "=VLOOKUP(B2,sheet2!A:B,2,false)"
C2 will then contain the category your food stuff is meant to have.
You could then put a formula in D2 to check whether C2 is the same as A2, or
you could roll this into one by changing the formula in C2
to:"=VLOOKUP(B2,sheet2!A:B,2,false)=A2".

Then conditional formating on C2 with yellow if it's FALSE.

I hope this helps


-- ----
Glenton
www.leviqqio.com
Quality financial modelling