Data Validation

Brian from Maui · Mar 16th, 2002, 06:16 PM

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 · Mar 16th, 2002, 06:19 PM

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 · Mar 16th, 2002, 06:21 PM

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 · Mar 16th, 2002, 06:23 PM

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 · Mar 16th, 2002, 07:04 PM

Aloha Brian

I'm confused here, why not just use:

=A1>1800

Where A1 is the Validated cell.

Brian from Maui · Mar 16th, 2002, 07:09 PM

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 · Mar 16th, 2002, 07:17 PM

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 · Mar 16th, 2002, 07:29 PM

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 · Mar 16th, 2002, 07:43 PM

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 · Mar 16th, 2002, 07:54 PM

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

Mar 16th, 2002, 06:21 PM	#3
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	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. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 16th, 2002, 06:16 PM	#1
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 16th, 2002, 06:19 PM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	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 ]

Mar 16th, 2002, 06:23 PM	#4
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 16th, 2002, 07:04 PM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Aloha Brian I'm confused here, why not just use: =A1>1800 Where A1 is the Validated cell.

Mar 16th, 2002, 07:09 PM	#6
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 16th, 2002, 07:17 PM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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

Mar 16th, 2002, 07:29 PM	#8
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 16th, 2002, 07:43 PM	#9
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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

Mar 16th, 2002, 07:54 PM	#10
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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