I have forms that i fill out many times daily. Its basic setup is as below:

Date WAT# IAT# SN#
10/3/07 7:45 AM 40000106 0001758365 8F06481

I have a seperate sheet in my notebook that automatically populates the
serial number and IAT# when I enter the WAT#. what i am looking for is a way
to reduce the time spent editing, copying, and pasting. First problem is the
IAT# is always listed as a 7 digit number in the database, but in reality it
is 10 digits with me having to maually add the "000" before the 7 digit
number. Is there a way to do this? Second is the date and time. Is there a
way to enter the formula =now() and then have it automatically cut and paste
itself after each entry so I dont have to worry about the time stamp changing
after each close/reopen of my notebook? Finally, when I enter the WAT# I have
a vlookup that populates the other 2 serial number fields. But if I enter
anything else I need the other 2 fields to automatically populate the word
"parcel". Is there a way to manipulate the vlookup formula to accomplish
this?

Thanks for your help. I know this is quite a laundry list.



--
Clark

You can format your IAT# cells using a custom format of "0000000000"
and this will give you 3 leading zeroes in front of 7 digits you
enter.

For the date/time, you can do CTRL-semicolon followed by a <space
followed by CTRL-SHIFT-semicolon to give a date/time-stamp in the
cell. Format the cell(s) how you want them to appear.

You could modify your vlookup formula along the lines of:

=IF(ISNA(vlookup( ... )),"parcel",vlookup( ... ))

Hope these help.

Pete


On Oct 3, 3:06 pm, Clark wrote:
I have forms that i fill out many times daily. Its basic setup is as below:

Date WAT# IAT# SN#
10/3/07 7:45 AM 40000106 0001758365 8F06481

I have a seperate sheet in my notebook that automatically populates the
serial number and IAT# when I enter the WAT#. what i am looking for is a way
to reduce the time spent editing, copying, and pasting. First problem is the
IAT# is always listed as a 7 digit number in the database, but in reality it
is 10 digits with me having to maually add the "000" before the 7 digit
number. Is there a way to do this? Second is the date and time. Is there a
way to enter the formula =now() and then have it automatically cut and paste
itself after each entry so I dont have to worry about the time stamp changing
after each close/reopen of my notebook? Finally, when I enter the WAT# I have
a vlookup that populates the other 2 serial number fields. But if I enter
anything else I need the other 2 fields to automatically populate the word
"parcel". Is there a way to manipulate the vlookup formula to accomplish
this?

Thanks for your help. I know this is quite a laundry list.

--
Clark

Thanks for the hot key method for time stamping. I tried to record it in a
macro so I could have a hot key for it, but it returns the time stamp for
when the macro was recorded. Do you know a way to do this?

Thanks for the =ISNA formula it works like a champ!

How do you setup the custom format for adding "000" in front of a seven
digit number?
--
Clark


"Pete_UK" wrote:

You can format your IAT# cells using a custom format of "0000000000"
and this will give you 3 leading zeroes in front of 7 digits you
enter.

For the date/time, you can do CTRL-semicolon followed by a <space
followed by CTRL-SHIFT-semicolon to give a date/time-stamp in the
cell. Format the cell(s) how you want them to appear.

You could modify your vlookup formula along the lines of:

=IF(ISNA(vlookup( ... )),"parcel",vlookup( ... ))

Hope these help.

Pete


On Oct 3, 3:06 pm, Clark wrote:
I have forms that i fill out many times daily. Its basic setup is as below:

Date WAT# IAT# SN#
10/3/07 7:45 AM 40000106 0001758365 8F06481

I have a seperate sheet in my notebook that automatically populates the
serial number and IAT# when I enter the WAT#. what i am looking for is a way
to reduce the time spent editing, copying, and pasting. First problem is the
IAT# is always listed as a 7 digit number in the database, but in reality it
is 10 digits with me having to maually add the "000" before the 7 digit
number. Is there a way to do this? Second is the date and time. Is there a
way to enter the formula =now() and then have it automatically cut and paste
itself after each entry so I dont have to worry about the time stamp changing
after each close/reopen of my notebook? Finally, when I enter the WAT# I have
a vlookup that populates the other 2 serial number fields. But if I enter
anything else I need the other 2 fields to automatically populate the word
"parcel". Is there a way to manipulate the vlookup formula to accomplish
this?

Thanks for your help. I know this is quite a laundry list.

--
Clark

Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub

If you wanted, you could set up event code that would stamp date or time or both
whenever you enter the WAT# without using a button or keyboard.

If interested post back.

To prepend 000 to a seven digit number FormatNumberCustom.

Enter 10 0's


Gord Dibben MS Excel MVP

On Wed, 3 Oct 2007 10:45:02 -0700, Clark
wrote:

Thanks for the hot key method for time stamping. I tried to record it in a
macro so I could have a hot key for it, but it returns the time stamp for
when the macro was recorded. Do you know a way to do this?

Thanks for the =ISNA formula it works like a champ!

How do you setup the custom format for adding "000" in front of a seven
digit number?

Thanks for the prepend info! works like a charm.

I am interested in the below routine. My date coloum is coloum B. does the
below need to be modified to effect B alone? i need a stamp that has both
time and date.
--
Clark


"Gord Dibben" wrote:

Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub

If you wanted, you could set up event code that would stamp date or time or both
whenever you enter the WAT# without using a button or keyboard.

If interested post back.

To prepend 000 to a seven digit number FormatNumberCustom.

Enter 10 0's


Gord Dibben MS Excel MVP

On Wed, 3 Oct 2007 10:45:02 -0700, Clark
wrote:

Thanks for the hot key method for time stamping. I tried to record it in a
macro so I could have a hot key for it, but it returns the time stamp for
when the macro was recorded. Do you know a way to do this?

Thanks for the =ISNA formula it works like a champ!

How do you setup the custom format for adding "000" in front of a seven
digit number?

The two subs I posted work only on the active cell. You could combine into one
sub.

One button for one macro to enter the date and time in the active cell.

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "dd-mm-yyy h:mm:ss AM/PM")
End Sub

But......................maybe better is event code.

To have the date and time automatically in column B when you enter a WAT# in
Column C

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("C:C")) Is Nothing Then
Target.Offset(0, -1).Value = Format(Now(), "dd-mm-yyyy h:mm:ss AM/PM")
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click and "View Code".

Copy/paste the above into that sheet module.

When you enter a WAT# in column C the static date and time will appear in same
row in column B


Gord

On Wed, 3 Oct 2007 11:57:01 -0700, Clark
wrote:

Thanks for the prepend info! works like a charm.

I am interested in the below routine. My date coloum is coloum B. does the
below need to be modified to effect B alone? i need a stamp that has both
time and date.
--
Clark


"Gord Dibben" wrote:

Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub

If you wanted, you could set up event code that would stamp date or time or both
whenever you enter the WAT# without using a button or keyboard.

Thanks again!!!

Now one last question: since each page in my workbook corresponds to one day
of activity is there any way to make the routine apply to the whole workbook?
or do i need to copy/paste the routine as code for each worksheet?
--
Clark


"Gord Dibben" wrote:

The two subs I posted work only on the active cell. You could combine into one
sub.

One button for one macro to enter the date and time in the active cell.

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "dd-mm-yyy h:mm:ss AM/PM")
End Sub

But......................maybe better is event code.

To have the date and time automatically in column B when you enter a WAT# in
Column C

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("C:C")) Is Nothing Then
Target.Offset(0, -1).Value = Format(Now(), "dd-mm-yyyy h:mm:ss AM/PM")
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click and "View Code".

Copy/paste the above into that sheet module.

When you enter a WAT# in column C the static date and time will appear in same
row in column B


Gord

On Wed, 3 Oct 2007 11:57:01 -0700, Clark
wrote:

Thanks for the prepend info! works like a charm.

I am interested in the below routine. My date coloum is coloum B. does the
below need to be modified to effect B alone? i need a stamp that has both
time and date.
--
Clark


"Gord Dibben" wrote:

Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub

If you wanted, you could set up event code that would stamp date or time or both
whenever you enter the WAT# without using a button or keyboard.

You can cut the event code from the worksheet and place it in ThisWorkbook
module.

To acces ThisWorkbook module, right-click on the Excel Icon left of "File" on
worksheet menu bar and select "View Code".

Once pasted..........................

Change Private Sub Worksheet_Change(ByVal Target As Range) line

to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Will now run for all sheets.


Gord



On Thu, 4 Oct 2007 09:54:11 -0700, Clark
wrote:

Thanks again!!!

Now one last question: since each page in my workbook corresponds to one day
of activity is there any way to make the routine apply to the whole workbook?
or do i need to copy/paste the routine as code for each worksheet?