data validation formula (Excel2003)

Topic

New Reply

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

Reply | Quote

Replies

Hi Zeddy

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

Reply | Quote

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

Reply | Quote

(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.)

Great just checking before I went into a full blown idea

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 =C3C2 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

Reply | Quote

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

Reply | Quote

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) 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

Reply | Quote

Thanks Hans

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

regards

zeddy

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

Gotcha – I see now that I didn’t read your 2nd reply closely enough. Glad that it may be helpful though.
Regards,

Reply | Quote

I always find all replies useful.
There are so many ways of getting the end result with Excel.
And someone always comes up with something you hadn’t thought of or had forgotten.
And when I see a particulary elegant solution to someone else’s issue I try and file it away in my brain for future use.
This is a terrific site and the MVPs supporting it are truly amazing.
Thanks and thanks again to all of you.

zeddy

Reply | Quote

Hi Hans:

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

Tony.

Reply | Quote

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.

Reply | Quote

Hans:

Many thanks – also for ‘specifications’ in help.

Tony.

Reply | Quote

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

zeddy

Reply | Quote

Sorry Hans

The Dim f seems to have gone.

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

zeddy

Reply | Quote

Zeddy:

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

Perhaps I’m being Dim?

Tony.

Reply | Quote

Hi Tony

You have to be very quick to catch Hans napping.
I printed a copy of his original posted solution and have the evidence!
One day I’ll learn how to edit my posts too.

zeddy

Reply | Quote

Dim f was a leftover from an idea that I abandoned. In my original reply, I had forgotten to remove it. I noticed this later on, and edited my post by clicking the Edit Post button in the post header.

Reply | Quote