Data Validation

[Brian from Maui]

Aloha,

Is it possible to set a data validtion where that cell has the sum of other cells. I've tried using custom and using the formula to sum cells and greater than, but evertime test it with a value greater than what I want, I get no warning prompt.



Brian

 

[smozgur]

a1, a2, a3, a4 and a5 has data and b1 is the validation cell.

On B1 Tools_Validation
Select Custom
Formula : >SUM(A1:A5)

and your message etc...

regards
suat
This message was edited by smozgur on 2002-03-16 17:20

 

[Jack in the UK]

Hi Brian

Dont see why not!! Ill have a play now 2AM London... a bit later should work out fine...
Set the validation to cell X i guess cell X will be formula so on calculatuin the warning will appear, ckeck all set up ok..



Take care friend.

 

[Brian from Maui]

What I want is to set the validation cell of the sum of the cells to say >1800, however the sums may not always excedd 1800.

Brian

 

[Dave Hawley]

Aloha Brian

I'm confused here, why not just use:

=A1>1800

Where A1 is the Validated cell.

 

[Brian from Maui]

Dave,

A1 is the sum of say B1:B3. It may not always total 1800, but if it does, I'd like than warning to come out!

Brian

 

[Dave Hawley]

Ok I see, you want the cell containing the SUM function to display a warning if it's value is less than 1800! Cannot be done with Data Validation, sorry. Use this code in the Private Module of the Sheet Object


Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.Sum(Range("A1")) <> 1800 Then
MsgBox "Invalid value", vbCritical
Target.Select
End If
End Sub

 

[Brian from Maui]

Dave,

Thanks for the code. I really am new to Excel and the users who will be using this workbook are too. I have a conditional format in the adjacent cell that say wt exceeded. For now until I'll use he conditional formatting one. Mahalo for your time, I've spent the better part of two hours trying to figure thsi one out.

Brian

 

[Dave Hawley]

Brian

That's ok. The user would have to do anything though. You just pop in the code and it's done!

But having said this, how about this alternative.

Instead of validating the SUM cell (A1) how about validating the cells the SUM function is refencing with something like:

=SUM($A$1)>1800

Set the Error altert to something other than high so they can still enter a number that may cause A1 to return a value less than 1800. Just in case they need to enter another number that WILL cause it to SUM greater than 1800

 

[Brian from Maui]

Dave,

I've tried the code you gave me. I like it!
My first time with something like this. I wold like to expand his to other cells. Do I just copy this again for each cell?

Brian