Formula Help

  • Thread starter Thread starter Nick Read
  • Start date Start date
N

Nick Read

Is it possible for me to write a formula that ensures that
there is data in the previous cell before data can be
entered into it.

e.g. for users to enter data into cell b1 data must first
be entered into cell a1.

If it is possible I would be grateful if you could provide
me with the formula.

Ta

Nick
 
Hi Nick
try the following:
- select cell B1
- goto 'Data - validation' and choose 'Custom'
- enter the formula
=A1<>""
- uncheck 'ignore blank cells' in this dialog
 
This may be all that the OP needs, but if this is for others to use, I'd
use this instead:

=LEN(TRIM(A1))>0


to avoid the frequent problem of people "deleting" a cell by entering a
space character.

I would also add conditional formatting with the same formula to
indicate that the cell was not ready for an entry.

Of course, if the OP had particular data in mind (say numeric) this
could be even more restrictive:

=ISNUMBER(A1)

And, for better user interface design, instead of getting an error
message *after* the attempt to enter data in B1, if the OP was willing
to password protect the sheet, the cell could be locked to prevent
entry, and an event macro (in the worksheet code module) used to
validate the entry in A1 and then unlock B1:

Private Sub Worksheet_Change(ByVal rCell As Excel.Range)
Const sMSG As String = "Please enter a number between 10 and 100"
Const sPWORD As String = "drowssap"
With rCell(1)
If Not .Address(False, False) = "A1" Then Exit Sub
If IsNumeric(.Value) Then
If .Value >= 10 And .Value <= 100 Then
Me.Unprotect sPWORD
Range("B1").Locked = False
Me.Protect sPWORD
Else
Me.Unprotect sPWORD
With Range("B1")
.Locked = False
.ClearContents
.Locked = True
End With
Me.Protect sPWORD
MsgBox sMSG
End If
End If
End With
End Sub
 
Back
Top