Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Data Validation

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  4. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha Brian

    I'm confused here, why not just use:

    =A1>1800

    Where A1 is the Validated cell.



  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •