Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    data validation formula (Excel2003)

    I have a data block of 20 columns by 100 rows (starting at row 10, column P)

    The data block should contain +ve numbers or empty cells only.
    Within this data block, a row may be completely empty.
    If the data row has any numbers, they should be consecutively smaller (but not necessarily in adjacent columns)

    I have spare rows above and spare columns to the left of this data block where I could put check formulas (or functions).

    Any suggestions for a quick way to put a validation check in?
    Should I use formulas, custom function or a VBA loop??
    I'm looking for an inspiration.


    zeddy

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: data validation formula (Excel2003)

    Hi Zeddy

    Just checking. Do you only want positive numbers and blanks but no negatives?
    Jerry

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Hi Jerry
    Yes.

    The data *should* be either +ve numbers or blanks
    Essentially, I want a check value to confirm there are no funny entries (-ve or text) and that any subsequent numbers in a row are not larger than any previous entries on that row (within the data block range).
    It would be OK for zeros and I would tolerate duplicate numbers e.g. 1000 followed by 800 followed by 800 followed by 800 followed by 750, .. 700, .. 625 etc would be OK

    This data is being imported for checking, so I'm not looking for a data entry validation rule or conditional format solution.

    zeddy

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: data validation formula (Excel2003)

    <P ID="edit" class=small>(Edited by Jezza on 13-Dec-06 22:04. Whhops realised too late that you did not want Data validation, but it will still work if it is imported as raw data and you have a validation worksheet.)</P>Great just checking before I went into a full blown idea <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You can use Conditional Formatting for this:

    1) Select the C3: C11 (I had data in C2:C11)
    2) Click Format|Conditional Formatting
    3) Select Formula is
    4) Type =C3<C2
    5) Select a colour ( I chose Green for good)


    Press OK. If the data is now lower than the one above then it turns red. Similarly you could change the value of the formula to =C3>C2 and select red (for bad)

    As you have gaps in rows you should just arrange the formula =C3<C1 and that wouldallow the gaps...my starters
    Jerry

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Thanks Jerry

    Now let me explain more clearly.
    The data is to be checked across the columns in a particular row.
    Cells in the row must be not be larger than any previous cell in that row.
    Empty cells within the row are allowed.
    So you could have say, 1000 in cell [P15], followed by several blank cells in that row until, say cell [T15] which might have 825 (thats OK because it's smaller) etc.

    Conditional formatting won't help me in this case - I'm automating the import of this block of data, if it follows the validation rules I'll do something with it and if it doesn't, I do something else.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Thanks Hans

    I shall explore your suggestion.
    Looks good.
    I might take a break and re-think it.

    regards

    zeddy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data validation formula (Excel2003)

    You can adapt the following macro to suit your purposes (for example write something to the Debug window or to another sheet instead of displaying message boxes).

    Sub CheckData()
    Dim r As Long
    Dim c As Long
    Dim m As Double
    For r = 10 To 109
    m = 9.99999999999999E+307
    For c = 16 To 35
    If IsNumeric(Cells(r, c)) Then
    If Cells(r, c) < 0 Then
    MsgBox "Cell " & Cells(r, c).Address(False, False) & _
    " contains a negative value.", vbExclamation
    ElseIf Cells(r, c) = "" Then
    ' nothing
    ElseIf Cells(r, c) = 0 Then
    MsgBox "Cell " & Cells(r, c).Address(False, False) & _
    " contains a zero value.", vbExclamation
    Else ' must be positive
    If Cells(r, c) > m Then
    MsgBox "Cell " & Cells(r, c).Address(False, False) & _
    " is larger than a previous value.", vbExclamation
    ElseIf Cells(r, c) < m Then
    m = Cells(r, c)
    End If
    End If
    Else
    MsgBox "Cell " & Cells(r, c).Address(False, False) & _
    " is not numeric.", vbExclamation
    End If
    Next c
    Next r
    End Sub

  8. #8
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation formula (Excel2003)

    Hi,
    II think you can use Data Validation to do what you want, if I've understood it correctly. In the attached I put the data in row 5 starting in column A, then selected the data cells to the right of A in that row and entered the data validation formula
    =NOT(OR(AND(B5>MIN($A5:A5),MAX($A5:A5)>0),B5<0))
    The range expands to check all cells to the left. The MAX part is to make sure that there is a numerical entry to the left.
    This does not, however, look to the right at all, to see if a new entry will make entries to the right of it invalid (because they are higher than the new entry.) You could Validate against that, or use a similar formula with conditional formatting just to flag those cells when they become invalid.

    Regards,

  9. #9
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation formula (Excel2003)

    Hi Hans:

    Looking through your code. Can you explain to me why the assignment m = 9.99999999999999E+307 please?

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data validation formula (Excel2003)

    m is the minimum positive value encountered in the row. At the beginning of the loop for each row, it is initialized to 9.99999999999999E+307 because that is the largest number that can be entered in a cell, so each value you encounter will be less than that. I use this to avoid an extra test: if you encounter a positive number for the first time, set m to this number, otherwise compare the number to m. Because m has been initialized to the highest possible value, I only have to compare each value to m.

    Type specifications in the help box, then select 'Excel specifications and limits' > 'Calculation specifications' to see 9.99999999999999E+307 mentioned.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation formula (Excel2003)

    Hans:

    Many thanks - also for 'specifications' in help.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Yes, but what was the
    Dim f
    for???

    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Sorry Hans

    The Dim f seems to have gone.

    The solution you provided did everything I wanted.
    Many thanks indeed.

    zeddy

  14. #14
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation formula (Excel2003)

    Zeddy:

    I can't see a Dim f anywhere in Hans's code.

    Perhaps I'm being Dim?

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: data validation formula (Excel2003)

    Hi JogoDoBicho

    Thanks for taking time to review my request.
    Your suggestion was excellent - I will suggest this to the person who created the original template data sources.
    I much prefer to use data validation with conditional formatting at the 'front-end' when data is first being 'entered'.
    However, my problem was that the data had already been created and I needed to do automatic 'post data-entry' checking and verification on many hundreds of these data blocks
    Using the Hans method allowed me to test for all the conditions I needed.

    Regards
    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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