Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.

maybe
=if(left(b5,4)=2006,a4+1,"")
--
Don Guillett
SalesAid Software

"Rao Ratan Singh" wrote in message
...
Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.

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?

"Rao Ratan Singh" wrote:

Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.

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

--
Regards,
Fred


"Rao Ratan Singh" wrote in message
...
Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.

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

--
*****
birds of the same feather flock together..



"Rao Ratan Singh" wrote:

Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.

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(B50,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

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

"Fred Smith" wrote:

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

--
Regards,
Fred


"Rao Ratan Singh" wrote in message
...
Hi there,
I m keeping inventory record in excel sheet using my Item ID like this -
200600001 and so on by entering formula==IF(B50,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.