Warning Message

[Sourdust]

How would I display a warning if a line contained any value greater or lesser than zero? In simple terms a learner would understand if possible. Thanks

 

[Mudface]

Select the cell(s) you require, click on Data-Validation and change the Allow dropdown from 'Any Value' to 'Whole Number'. Change the Data dropdown to 'Equal' and put a zero in the Value box. Click on the Error Alert tab and change the Style dropdown to 'Warning', enter a message if you like, then click on OK. Now, whenever someone enters a value which isn't 0 in the cells you selected a warning message appears. HTH.

 

[Andrew Poulsom]

A couple of questions:

1. At what stage would you like to display the warning? When data is entered or when the workbook is calculated?
2. What range do you want to check?

 

[Sourdust]

That works perfectly thanks very much Mudface . I hope Hull isn't so wet and miserable today. It is in Edinburgh

Thanks for replying as well Andrew. I want the warning to appear when the cells are recalculated and the formula in each cell in the line are totals of the ranges above minus ranges in another workbook which should be equal. It is a check that all changes in a number of integrated workbooks have been done correctly to save me having to trawl through each of them looking for mistakes. I've been known to make the odd mistake or two in my time LOL

Thanks again to both of you.

 

[Sourdust]

ADVERTISEMENT

Sorry for having to ask for help again but the data validation suggested by Mudface worked yesterday but not today It is to show a warning when a cell totals any figure other than zero. The cell calculates the difference between a total which is only changed manually and another which is updated by changes in other workbooks.

 

[Andrew Poulsom]

I think data validation only works with user entry, not with the results of formulas.

So what range are you checking?

 

[Sourdust]

ADVERTISEMENT

I'm not sure if this answers your question properly but a range of cells in the same row with the formula as described above.

Thanks Andrew

 

[Andrew Poulsom]

No, I need the range you want to check eg A1:Z1.

 

[s-o-s]

On 2002-10-15 05:39, Sourdust wrote:
How would I display a warning if a line contained any value greater or lesser than zero? In simple terms a learner would understand if possible. Thanks

Hi,

Whatever the column you are expecting to return a value of zero in select these cells

Format | Conditional Formatting

Select Cell Value Is | Not Equal to | 0

Click the format button and set the Pattern (background colour to Red)

This will now show all the cells (assuming you selected them all) that have a non zero value.

 

[Sourdust]

Andrew, the range is B182 to AT182. Thanks for your continued interest and patience.

Thanks for the suggestion Sean but formatting seems to be the same as validation. As Andrew says it only works with user entry not changes brought about by a formula. A useful thing to know for the future though