Data Validation?

[n.pierce]

I'm not an excel expert but I am a fast learner and do enjoy excel
But I'm struggling with this:

I8 has this formula =-(BUDGET!C54) which is the beginning budget amount
I9 has this formula =SUM(I12:I50) which is total of the expenses entered
I10 has this formula =I8-I9 which is the difference between budget and expenses

I want to create a message/warning in for cells I12 through I50 that will warn the user if they are over budget. For example, if they have a balance of $100 in the budget but enter an expense for $105 I want it to alert them of this.

I have tried to validate the cell by allowing data to be entered that is LESS than or EQUAL to the budget amount (I8) but that doesn't work....

I'm not sure if I'm explaining myself very well...I hope so! Any help is GREATLY appreciated!

 

[xenou]

I think you create a circular reference problem here...you want to validate based on the sum of I12-I50, but the cell you are changing will change the sum of I12-I50....

This could be done with VBA but its probably overkill. What about setting a conditional format in the budget variance (i.e., highlight red when it becomes negative...)?

Alex.

 

[Peter_SSs]

n.pierce

Welcome to the Mrexcel board!

If I have understood correctly, I think you can do this.

Select I12:I50 and in Data Validation in the Allow box choose 'Custom' and enter this formula in the Formula box: =$I$10>=0

While in the Data Validation, on the Error Alert tab put a message like: Gone over budget!

 

[n.pierce]

THANK YOU THANK YOU THANK YOU! That works perfect! I knew it couldn't be that difficult but sometimes i make it a bit harder than it is! You made my day!

 

[xenou]

Glad you were here Peter. I'll have to try this out tonight too so I understand it better.

Alex

 

[mfexcel]

i learn sth new to me every day here...

 

[inarbeth]

Alternatively you could use a conditional format to change the background to bright red.