+ Reply to Thread
Results 1 to 3 of 3

Data Validation with Custom function and message box display

  1. #1
    Registered User
    Join Date
    01-07-2014
    Location
    Bothell, Washington
    MS-Off Ver
    Excel 2010
    Posts
    8

    Data Validation with Custom function and message box display

    Hello there,

    I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
    for instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?

    Assumptions:
    Will ignore blanks
    Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)

    Scenarios:
    c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
    c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
    c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
    etc

    What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values.
    Can this be done using Data Validation in Excel 2010?

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,915

    Re: Data Validation with Custom function and message box display

    Here is the custom data validation formula to use, for all four cells:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,915

    Re: Data Validation with Custom function and message box display

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

    Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.

    If you solved your own problem, please provide your solution so that somebody seeing this thread in a search for the same problem will see how it was solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Add a help message to a custom added function
    By johnbtic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2012, 12:51 PM
  2. need of pop up message dynamically using data validation input message method
    By vba_life in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2010, 09:03 AM
  3. display a message, before calling the function
    By shapper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2008, 04:12 PM
  4. [SOLVED] Why error message when trying to display custom view?
    By creativeops in forum Excel General
    Replies: 2
    Last Post: 01-09-2006, 06:00 PM
  5. Custom function valid in Data Validation/Conditional Format?
    By Tetsuya Oguma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1