Excel Formula Help?

[Tim Bento]

Hi,

I am trying to track invoice numbers on Excel for a small company. The
numbers appear with the year first followed by the 4 digit sale number (I.E.
the first sale of 2003 would appear as 03-0001).

There are several thousand invoices, and some are missing. I was attempting
to create a formula that would take the invoice number, then add one digit
for the invoice number that is supposed to appear next (03-0001 + 1=
03-0002). But I can't seem to get it to work without #value appearing in the
cell.

What am I overlooking in this?

Thanks,
Tim

 

[James Silverton]

Hi,

I am trying to track invoice numbers on Excel for a small company. The
numbers appear with the year first followed by the 4 digit sale number (I.E.
the first sale of 2003 would appear as 03-0001).

There are several thousand invoices, and some are missing. I was attempting
to create a formula that would take the invoice number, then add one digit
for the invoice number that is supposed to appear next (03-0001 + 1=
03-0002). But I can't seem to get it to work without #value appearing in the
cell.

What am I overlooking in this?

Thanks,
Tim

With the receipt number formatted as text in A1,

B1 =LEFT(A1,3)&TEXT((RIGHT(A1,4)+1),"0000")

sems to work.

Jim.

 

[Tim Bento]

Thanks for the response Jim,

But I can't get it to work. What does the 3 in (A1,3) and 4 in (A!,4)
effect?

-Tim

 

[Mike Williams [MVP]]

Please respond in the same thread on this newsgroup. Make sure you include
details of your application and Windows versions, and whether or not you
have included any service pack updates.

Thanks for the response Jim,

But I can't get it to work. What does the 3 in (A1,3) and 4 in (A!,4)
effect?

If you check Excel's help for the functions LEFT() and RIGHT(), you'll see
that in the context of

B1 =LEFT(A1,3)&TEXT((RIGHT(A1,4)+1),"0000")

the 3 means the leftmost 3 characters of the cell A1, and the 4 means the
rightmost 4 characters of the cell A1.