Automatic Cell Filling

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I've done this before but can't remember how i got there.

I have two columns (A and B). I have to enter a date in Column B and this
needs to automatically enter the number 1 into its neighbouring cell in
Column A. For instance if B6 had a date in it then A6 would show the number 1.
Sounds simple but Column A feeds into other spreasheets so if its not filled
in other data will be wrong. By having it filled automatically will hopefully
eliminate human error.

Thanks for your help
 
Put this in A1:

=IF(B1="","",1)

and copy down.

This just checks for the cell being empty, and returns 1 if it isn't,
but of course you could put anything in B1 and it would make A1 show
1. You could change the formula and include an ISNUMBER or ISTEXT
function to disallow text, but the problem with saying that it has to
be a date is that a date is just a number to Excel, so it could not
distinguish between 1234, for example, and 01/01/2008.

Hope this helps.

Pete
 
One way is to right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If IsDate(Target) Then Target.Offset(, -1) = 1
End Sub
 
Back
Top