software

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(2006amp;MOD(A1,20060) 1)

adjust for A1 to where your value is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;scottquot; gt; wrote in message
news
gt; In my spreedsheet we have to record daily test results with an id numbered
gt; assigned to them. To start the new year we would start with 20061. 2006
gt; represents the year and 1 represents the first sample. I start the year
with
gt; this number and then in the following cells my formula just adds one. This
gt; elminates us typing in the id number so the next number would be 20062
etc. I
gt; did not take into account that once I reached 20069 my next ID number
turned
gt; to 20070. I need to keep the 2006 so after 20069, the next reading would
be
gt; 200610. Is there a way to do this either through formating or a different
gt; formula.
gt;
gt; thanks in advance
gt; scott
How about making column A display quot;2006quot; all the way down, the make a column
B with your numbers (ie 1-1000), then make a column c with the formula a1amp;b1?
You can hide columns A and B and C will display your id.

hope that helps

quot;scottquot; wrote:

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


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

You can try overwriting your codes with:

=YEAR(TODAY())amp;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: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=497912Thanks 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

quot;Bob Phillipsquot; wrote:

gt; Scott,
gt;
gt; Try this
gt;
gt; =VALUE(2006amp;MOD(A1,20060) 1)
gt;
gt; adjust for A1 to where your value is
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;scottquot; gt; wrote in message
gt; news
gt; gt; In my spreedsheet we have to record daily test results with an id numbered
gt; gt; assigned to them. To start the new year we would start with 20061. 2006
gt; gt; represents the year and 1 represents the first sample. I start the year
gt; with
gt; gt; this number and then in the following cells my formula just adds one. This
gt; gt; elminates us typing in the id number so the next number would be 20062
gt; etc. I
gt; gt; did not take into account that once I reached 20069 my next ID number
gt; turned
gt; gt; to 20070. I need to keep the 2006 so after 20069, the next reading would
gt; be
gt; gt; 200610. Is there a way to do this either through formating or a different
gt; gt; formula.
gt; gt;
gt; gt; thanks in advance
gt; gt; scott
gt;
gt;
gt;

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)

quot;scottquot; gt; wrote in message
...
gt; Thanks Bob that looks like it will work perfect. Do you see any problems
the
gt; larger the number gets. Last year we ran 1944 samples would anything
happen
gt; if say this gets to 1999.
gt; thanks again Scott
gt; One other question. I ahve atemplate for my ss. Is there a tweak to your
gt; formula that would replace the 2006 with the current year, for future.
When
gt; 2007 comes around I would have to reset 2006 to 2007 just wondering
gt;
gt; thanks again scott
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Scott,
gt; gt;
gt; gt; Try this
gt; gt;
gt; gt; =VALUE(2006amp;MOD(A1,20060) 1)
gt; gt;
gt; gt; adjust for A1 to where your value is
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;scottquot; gt; wrote in message
gt; gt; news
gt; gt; gt; In my spreedsheet we have to record daily test results with an id
numbered
gt; gt; gt; assigned to them. To start the new year we would start with 20061.
2006
gt; gt; gt; represents the year and 1 represents the first sample. I start the
year
gt; gt; with
gt; gt; gt; this number and then in the following cells my formula just adds one.
This
gt; gt; gt; elminates us typing in the id number so the next number would be 20062
gt; gt; etc. I
gt; gt; gt; did not take into account that once I reached 20069 my next ID number
gt; gt; turned
gt; gt; gt; to 20070. I need to keep the 2006 so after 20069, the next reading
would
gt; gt; be
gt; gt; gt; 200610. Is there a way to do this either through formating or a
different
gt; gt; gt; formula.
gt; gt; gt;
gt; gt; gt; thanks in advance
gt; gt; gt; scott
gt; gt;
gt; gt;
gt; gt;
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

quot;scottquot; wrote:

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

Format / Cells / Date

quot;tomquot; gt; wrote in message
...
gt;I entered the date as 04/01/06 into a cell. someone went in and did
gt; something because now the date shows up as 38808. How can I fix this
gt;
gt; quot;scottquot; wrote:
gt;
gt;gt; In my spreedsheet we have to record daily test results with an id
gt;gt; numbered
gt;gt; assigned to them. To start the new year we would start with 20061. 2006
gt;gt; represents the year and 1 represents the first sample. I start the year
gt;gt; with
gt;gt; this number and then in the following cells my formula just adds one.
gt;gt; This
gt;gt; elminates us typing in the id number so the next number would be 20062
gt;gt; etc. I
gt;gt; did not take into account that once I reached 20069 my next ID number
gt;gt; turned
gt;gt; to 20070. I need to keep the 2006 so after 20069, the next reading would
gt;gt; be
gt;gt; 200610. Is there a way to do this either through formating or a different
gt;gt; formula.
gt;gt;
gt;gt; thanks in advance
gt;gt; scott