Using Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,
How can I modify the formula below to autopopulate the date from Cell A2 to A500 when numbers are typed from B2 to B500?

=IF(ISNUMBER(B2), TODAY(),"")


I know that I can copy and paste the formula to each A-cell seperately, but then I'd have to change the (B2) designation, and that's a lot of work for 500 rows. There has to be an easier way. (Also, is there a way to paste a formula to more than one cell at a time? My Excel 2002 does not seem to like it when I try.)

Thanks and Happy Holidays!!!
 
From what I can tell your formula is fine.

To copy the formula to the 500 cells all you need to do is
drag the formula down. Type the formula in A2. Then, make
sure A2 is the active cell. Move the mouse to the lower
right corner of A2. When you see it change to a sorta-plus
sign, click and hold while pulling down with the mouse.

In general, you can select a cell, hit ctrl-C to copy.
Then select a range of cells and hit ctrl-v and the
formula (or value) will be copied to all the cells.

-----Original Message-----
Hello All,
How can I modify the formula below to autopopulate the
Click to expand...
date from Cell A2 to A500 when numbers are typed from B2
to B500?
=IF(ISNUMBER(B2), TODAY(),"")


I know that I can copy and paste the formula to each A-
Click to expand...
cell seperately, but then I'd have to change the (B2)
designation, and that's a lot of work for 500 rows. There
has to be an easier way. (Also, is there a way to paste a
formula to more than one cell at a time? My Excel 2002
does not seem to like it when I try.)
 
Ron in Tulsa said:
Hello All,
How can I modify the formula below to autopopulate the date from Cell A2
Click to expand...
to A500 when numbers are typed from B2 to B500?
=IF(ISNUMBER(B2), TODAY(),"")


I know that I can copy and paste the formula to each A-cell seperately,
Click to expand...
but then I'd have to change the (B2) designation, and that's a lot of work
for 500 rows. There has to be an easier way. (Also, is there a way to paste
a formula to more than one cell at a time? My Excel 2002 does not seem to
like it when I try.)
Thanks and Happy Holidays!!!
Click to expand...

I have a feeling that this is not going to do what you want! For every cell
in column B that has a number in it, you will have exactly the same date in
column A, and that date will be 'live' in the sense that today it will be
today's date and tomorrow it will be tomorrow's. If that's all you want, why
do you need so many copies of it? I think what you may want is to enter a
date in column A at the time when a number is entered in column B, and have
that date remain constant thereafter. If so, you cannot do this with a
workbook formula; you would need macro.
 
Ron

I think Paul is correct on his assessment.

Right-click on the sheet tab and copy/paste this code in there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("A" & n).Value = Now
End If
End If
enditall:
End Sub

Just for other info.....If you have a formula in say C1 like =A1+B1

Hover the mouse pointer over the bottom-right corner of C1 until you see a
black cross. Click on that cross and drag/copy down Column C.

The =A1+B1 will change to =A2+B2, =A3+B3 etc. as you drag it down.

Check out Help on Relative and Absolute references.

Gord Dibben Excel MVP
 
Back
Top