Implenting auto incrementing date in Excel?

[haemphyst]

I put this up in the MSOffice forum, and somebody told me you guys could help. So, first off, sorry for this question... I am a hardware and network guy, not an apps guy.

I need to build a spreadsheet, where the far left column is the date. I can fugure out all of the rest of the formulae I need, but what I cannot grasp is this: When the user reaches the last cell in the line, I would like them to just be able to hit enter, the cursor moves to the second cell of the next line, and the first cell auto fills the present date and time, as well as a second column with a "Receipt Number" that will increment one number at a time. Any suggestions? or is this going to be something I'll have to do in Access? (I hate Access).

Access also will probably not do, because they will occasionally need to print out the complete list of updates as they were entered, for audit purposes. This is why I chose Excel. I am willing to move the columns around in the spreadsheet, if it makes thing easier to do.

e-mail me at ddraper at igalaxy dot net

 

[SkipVought]

Hi,

This will trigger from the Worksheet_Change event.

right click the sheet tab and select View Code.

This will place you in the Sheet Code window.

In the upper LH of the window is the Object DropDown. Select Workbook.

In the upper RH of the window is the Procedure DropDown. Select Change.

You will see...

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This is where you code will go.

Meanwhile, back on the sheet, lets make it easy and put headings of Data1, Data2, Recorded Date and Receipt Number

Meanwhile, back in the code,

Private Sub Worksheet_Change(ByVal Target As Range)
  with target
    if .row = 1 then exit sub  'get out if you're in the heading row
    if .column > [A1].currentregion.columns.count then exit sub  'get out if you're to the right of the table
    for each h in range([A1], [A1].end(xltoright))
        select case .value
          case "Recorded Date"
            if cells(.row, h.column).value <> "" then 
               exit sub  ' get out if there's already a date
            else
               cells(.row, h.column).value = now
            end if
          case "Receipt Number"
            if cells(.row, h.column).value <> "" then 
               exit sub  ' get out if there's already a number
            else
               cells(.row, h.column).value = application.max(h.entirecolumn))+1
            end if
        end select
    next
  end with
End Sub

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]