Easiest way to flag data errors

[Prof Wonmug]

I have a sheet for calculating a bunch of values for allocating
resources among 2 or more people. The sheet has some limits on the
maximum values for certain results. For example, there may be a limit
of 10 pounds on some product. The actual allocations are determined by
various calculations such that it is possible for one of the results
to exceed the limit.

I would like to flag or highlight any results that are over the limit.
I don't especially care how this is done, but the cell needs to show
the incorrect value so I know how to make adjustments.

Probably the easiest method is to turn the cell data red.

I don't want to use the Text function because the cell contents may be
used in further calculations.

A further complication is that for some cells, there is more than one
possible error. The value could be over the limit or it might conflict
with some other value. Is there a way to generate multiple errors?

Thanks

 

[NelsonTan]

Hi Prof Wonmug,
Your question too complicated, suggest you display it more easy to
understand like:
ColA ColB ColC ColD ColE ......
1
2
3
4
5

 

[JLatham]

You can begin by looking into Conditional Formatting. You can have multiple
conditional formats for a variety of conditions: in pre-Excel 2007 versions
you can have 4 formats - 1 for nothing special going on, plus 3 for special
circumstances. With Excel 2007 more are available without resorting to VBA
to handle extra special cases.
In the simplest case where you want an alert if the result is above a
certain value, it's easy to set up. For comparisons to values of other
cells, you may want to return here for help setting them up - just be
specific in what you need.
Remember that in evaluating multiple conditions, the first one that matches
the criteria is acted on and the remaining are ignored, so the sequence you
enter them can be important. Example: you want a cell to turn yellow if the
value in it is more than 10, and turn red if it is more than 20. You would
need to test for more than 20 before testing for more than 10 to get the
expected result.