Automatic reference number for multiple ranges

Hi there,

I am currently putting a sheet together and I need to generate automatic reference numbers that are linked to particular companies. So when you type in your company, I need it to find the previous invoice for that particular company and then generate a new number.

E.g. Row A you type in McDonald's, and then in row B it will give you a new invoice/reference number specific to McDonald's.

If possible I would like the ref no. to be FR"Company no.""New number". So if McDonald's was company 5 and there had been 12 invoice numbers to date, the new number would be FR05.13?