Excel Help, Please.

stone7

I do my own bookkeeping and have a numbering system for all the receipts and invoices that I gather. For example, 14JA03. The 14 is the year, the JA is the month (January) and the 03 is the 3rd receipt/invoice when they are sorted chronologically. Some months I can have up to 50.

I currently type the receipt numbers individually ie 14JA01, 14JA02, 13JA03, and so on. Is there an easier way?

TIA.

sparkieguy

I think if you click and hold bottom right corner of the box and drag it down it auto fills the boxes

Mirno

If column A is your receipt number (use fill series as sparkieguy says), and B is the receipt date then:
=CONCATENATE(YEAR(B2),LOOKUP(MONTH(B2),Sheet3!$A$2:$A$13,Sheet3!$B$2:$B$13),TEXT(A2,"000"))

Will (assuming sheet3 has your mapping of month number (1-12) to text (JA-DE) in it) do what you need.

Note that I pad the receipt number to 3 digits using TEXT.

stone7

sparkieguy wrote: »

I think if you click and hold bottom right corner of the box and drag it down it auto fills the boxes

Brilliant. Works a treat.

I can't believe I've been typing them individually for the past 8 years or so.

Thanks again...... great help.