ID number as per Year

[Guest]

Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B5>0,A4+1,""). Presently 258
items i have entered in this finacial year and las ID number is 200600258.

But is there any way that for whole financial year 2006-07 it works in this
manner 200600001 and son and as just as next financial 2007-08 comes and what
product inventory falls in this new year it automatically starts from
200700001 and so on.

I hope for best simple formula.

 

[Don Guillett]

maybe
=if(left(b5,4)=2006,a4+1,"")

 

[Guest]

For the volumes you are using, I can't see why you need a formula.

In order to use a formula, we need to know when the financial year starts so
we can calculate the year; and how do we reset the count to 1?


For example

=IF(MONTH(NOW())>=4,YEAR(NOW()),YEAR(NOW())-1)

would set the year assuming the financial period starts in April

=TEXT(IF(MONTH(NOW())>=4,YEAR(NOW()),YEAR(NOW())-1),"0000")&TEXT($A$4,"00000")

would create an ID where A4 contained the item count e.g 258

Does this help?

 

[Fred Smith]

When does your fiscal year start? What determines what fiscal year you are in --
the system date or some other cell?

If you are using a calendar year, and the system date, change the "A4+1" part
of your formula to:

=year(today())*100000+mod(a4,100000)+1

 

[Guest]

Hi Singh,

by assuming while it seems the fiscal year starts around the mid calendar
year...

Col. B contains is the inventory table

? Col. A contains the Item ID....do you mean series ID ?
[then by assuming it is the ITEM ID] formulated to return the inventory
entry ID..

i suggest to use the same formula you have while preparing the inventory
item ID's per financial year per SHEET.

In sheet1 Year 1
the first item ID
say A2=200600001 : B2 = "your inventory"
then on A3 = if(B3="","",A2+1) :copy down to cells below
there shall be no blank row in between your records.

In sheet2 Year 2
the first item ID
say A2=200700001 : B2 = "your inventory"
then on A3 = if(B3="","",A2+1) :copy down to cells below
there shall be no blank row in between your records.

if you define the start date of fiscal year and have a column of reference
date on inventory record, maybe u can summariza all farther in a single
sheet..

regards

 

[Guest]

Sir,
In our country India Fiscal Year in the books usually people starts from
April and ends with March.

and at present 1st number i m setting with manually typing as 200600001 and
rest in downward with this formula IF(B5>0,A4+1,""). I m thinking that First
input inventory whatsoever product falls in 1st April 2007 its ID number
should be 200700001. So you suggest how i should go ahead.

Inventory format like this-
ProductID Product Name Cost Price Sale Price

Regards

 

[Guest]

Sir,
This is good formula and it working also. But 1. Fiscal year starts from
April to March. 2. If b4 is >0, a3+1,"") 3. According to System Date. So
please tell me how to set this formula.

Regards

Rao Ratan Singh