In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062 etc. I
did not take into account that once I reached 20069 my next ID number turned
to 20070. I need to keep the 2006 so after 20069, the next reading would be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott

Scott,

Try this

=VALUE(2006&MOD(A1,20060)+1)

adjust for A1 to where your value is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scott" wrote in message
...
In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year
with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062
etc. I
did not take into account that once I reached 20069 my next ID number
turned
to 20070. I need to keep the 2006 so after 20069, the next reading would
be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott

How about making column A display "2006" all the way down, the make a column
B with your numbers (ie 1-1000), then make a column c with the formula a1&b1?
You can hide columns A and B and C will display your id.

hope that helps

"scott" wrote:

In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062 etc. I
did not take into account that once I reached 20069 my next ID number turned
to 20070. I need to keep the 2006 so after 20069, the next reading would be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott

scott Wrote:
In my spreedsheet we have to record daily test results with an id
numbered
assigned to them. To start the new year we would start with 20061.
2006
represents the year and 1 represents the first sample. I start the year
with
this number and then in the following cells my formula just adds one.
This
elminates us typing in the id number so the next number would be 20062
etc. I
did not take into account that once I reached 20069 my next ID number
turned
to 20070. I need to keep the 2006 so after 20069, the next reading
would be
200610. Is there a way to do this either through formating or a
different
formula.

thanks in advance
scott

You can try overwriting your codes with:

=YEAR(TODAY())&ROW()+1

which assumes you are starting in row 2. If not, adjust the last part
(Row()+1) appropriately to coincide with the row number you begin in.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497912

Thanks Bob that looks like it will work perfect. Do you see any problems the
larger the number gets. Last year we ran 1944 samples would anything happen
if say this gets to 1999.
thanks again Scott
One other question. I ahve atemplate for my ss. Is there a tweak to your
formula that would replace the 2006 with the current year, for future. When
2007 comes around I would have to reset 2006 to 2007 just wondering

thanks again scott

"Bob Phillips" wrote:

Scott,

Try this

=VALUE(2006&MOD(A1,20060)+1)

adjust for A1 to where your value is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scott" wrote in message
...
In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year
with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062
etc. I
did not take into account that once I reached 20069 my next ID number
turned
to 20070. I need to keep the 2006 so after 20069, the next reading would
be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott

Scott,

I just ran it down to row 65536, no problems. There just aren't enough rows
in Excel (yet!) for it to become a problems.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scott" wrote in message
...
Thanks Bob that looks like it will work perfect. Do you see any problems
the
larger the number gets. Last year we ran 1944 samples would anything
happen
if say this gets to 1999.
thanks again Scott
One other question. I ahve atemplate for my ss. Is there a tweak to your
formula that would replace the 2006 with the current year, for future.
When
2007 comes around I would have to reset 2006 to 2007 just wondering

thanks again scott

"Bob Phillips" wrote:

Scott,

Try this

=VALUE(2006&MOD(A1,20060)+1)

adjust for A1 to where your value is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"scott" wrote in message
...
In my spreedsheet we have to record daily test results with an id
numbered
assigned to them. To start the new year we would start with 20061.
2006
represents the year and 1 represents the first sample. I start the
year
with
this number and then in the following cells my formula just adds one.
This
elminates us typing in the id number so the next number would be 20062
etc. I
did not take into account that once I reached 20069 my next ID number
turned
to 20070. I need to keep the 2006 so after 20069, the next reading
would
be
200610. Is there a way to do this either through formating or a
different
formula.

thanks in advance
scott

I entered the date as 04/01/06 into a cell. someone went in and did
something because now the date shows up as 38808. How can I fix this

"scott" wrote:

In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062 etc. I
did not take into account that once I reached 20069 my next ID number turned
to 20070. I need to keep the 2006 so after 20069, the next reading would be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott

Format / Cells / Date

"tom" wrote in message
...
I entered the date as 04/01/06 into a cell. someone went in and did
something because now the date shows up as 38808. How can I fix this

"scott" wrote:

In my spreedsheet we have to record daily test results with an id
numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year
with
this number and then in the following cells my formula just adds one.
This
elminates us typing in the id number so the next number would be 20062
etc. I
did not take into account that once I reached 20069 my next ID number
turned
to 20070. I need to keep the 2006 so after 20069, the next reading would
be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott