Hey Everyone...

I have a question for the group. In my work, I am responsible for
uploading data about a professionals continuing education record.
There are several ways that I can do this, but one of the more
fundamental ways, is by using a spreadsheet.

In this spreadsheet we have columns that you would typically find.
First Name, Last Name, Certification #, DOB, Course #, Class #, etc.

Each row in my spreadsheet represents one individual piece of coned
for one practitioner. I dont want to burdeon you with too many
details, so I cut to my question now. On fields, that are
representative of numbers, dates, or codes (course #'s and class #'s),
I must precede my data with an apostrophe ' . On a blank
spreadsheet, the cells have absolutely no formatting whatsoever. This
makes data entry inefficient and time consuming. But everytime I try
and format the cells, to include an apostrophe as the first character,
the spreadsheet will not upload properly. What does an apostrophe
do?

For a more detailed explanation of my process continue reading. I
will give you a hypothetical scenario. You hold a continuing
educaiton class. 10 people attend this class. For all ten people,
each person has the following attributes:

First Name
Last Name
DOB (mm/dd/yyyy)
Certificaiton # (6 digits with some preceding 0's)
Regional Code (2 digits with some preceding 0's)
Course # (6 digits with some preceding 0's)
Class # (6 digits with some preceiding 0's)
County # (2 digits with some preceding 0's)
Date of Class (mm/dd/yyyy)

Over the years, we have refined the reporting process to automate some
of these functions. However, there are occasions when hand entering
this information is necessary. An example would be, if you travel to
another state, take a ConEd class there, and would like to come back
to your home and have that CE Class applied to your practitioner
profile here. And since we operate on a yearly basis, everyone saves
up their last minute coned, for, you guessed it, December 31st.

When we put information into the spreadsheet, we save it
conventionally. We then use a program that "pushes" that data to the
states database. It then records the coned on the practitioenrs
profile. Unfortunately, I do not have many details on where that data
goes. I'm only guessing here that its a SQL Server database. And
since there are about 18 different organizations that push this data,
and some are more reponsible than others, details are difficult to
come by.

If I was able to automate the data entry process a little bit, by
formating these fields to only accept entries that conformed to rules,
that I can write, then the process would be launched forward. Anyone
have any thoughts?

Chip

The apostrophe make the value behave as text rather than number.
Times when this is necessary include:
1) an entry with more than 15 digits. Enter 1234567890123456789 and Excel
will round it to 15 digits and display it in scientific notation. Enter it
with '123..... and it is text
2) an entry what Excel might mistakenly treat as a date. Enter 4/5 and Excel
will give you a date but '4/5 is text. If you want four fifths then enter 0
4/5
3) to preserve a leading zero; but this can be done another way with a
custom number format
Hope this helps - my last 2008 contribution
Happy New Year
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chip" wrote in message
...
Hey Everyone...

I have a question for the group. In my work, I am responsible for
uploading data about a professionals continuing education record.
There are several ways that I can do this, but one of the more
fundamental ways, is by using a spreadsheet.

In this spreadsheet we have columns that you would typically find.
First Name, Last Name, Certification #, DOB, Course #, Class #, etc.

Each row in my spreadsheet represents one individual piece of coned
for one practitioner. I dont want to burdeon you with too many
details, so I cut to my question now. On fields, that are
representative of numbers, dates, or codes (course #'s and class #'s),
I must precede my data with an apostrophe ' . On a blank
spreadsheet, the cells have absolutely no formatting whatsoever. This
makes data entry inefficient and time consuming. But everytime I try
and format the cells, to include an apostrophe as the first character,
the spreadsheet will not upload properly. What does an apostrophe
do?

For a more detailed explanation of my process continue reading. I
will give you a hypothetical scenario. You hold a continuing
educaiton class. 10 people attend this class. For all ten people,
each person has the following attributes:

First Name
Last Name
DOB (mm/dd/yyyy)
Certificaiton # (6 digits with some preceding 0's)
Regional Code (2 digits with some preceding 0's)
Course # (6 digits with some preceding 0's)
Class # (6 digits with some preceiding 0's)
County # (2 digits with some preceding 0's)
Date of Class (mm/dd/yyyy)

Over the years, we have refined the reporting process to automate some
of these functions. However, there are occasions when hand entering
this information is necessary. An example would be, if you travel to
another state, take a ConEd class there, and would like to come back
to your home and have that CE Class applied to your practitioner
profile here. And since we operate on a yearly basis, everyone saves
up their last minute coned, for, you guessed it, December 31st.

When we put information into the spreadsheet, we save it
conventionally. We then use a program that "pushes" that data to the
states database. It then records the coned on the practitioenrs
profile. Unfortunately, I do not have many details on where that data
goes. I'm only guessing here that its a SQL Server database. And
since there are about 18 different organizations that push this data,
and some are more reponsible than others, details are difficult to
come by.

If I was able to automate the data entry process a little bit, by
formating these fields to only accept entries that conformed to rules,
that I can write, then the process would be launched forward. Anyone
have any thoughts?

Chip

Well, I'm betting that my case is door #3. Where I need to preserve
the leading 0. Can someone show me an example of a custom number
format for 6 digits, preserving the leading 0? I would think it would
be 000000. Or is it ######?

Thanks..!

Why not try those 2 options and see what you get? It won't cost you
anything to try, and it will help you to learn.
--
David Biddulph

Chip wrote:
Well, I'm betting that my case is door #3. Where I need to preserve
the leading 0. Can someone show me an example of a custom number
format for 6 digits, preserving the leading 0? I would think it would
be 000000. Or is it ######?

Thanks..!