Reference Number

Hello again, it's been a while.

I am currently creating a spreadsheet to record incoming files for my company. Each file must have a unique reference. Part of this reference will be made up of the file name and client name.

At present I have the following formula:

=LEFT(B4,1)&MID(B4,1,1)&LEFT(C4,1)&"_0001"

Which produces something similar to BBG_0001

Although this is a fairly unique reference already I need 0001 to be a unique number in its own right. This can be in any format provided it is a number and it has a minimum of 4 digits. It would be preferable for it to be in sequence but not essential.

It should be noted that once these files have been dealt with the row containing the information (including reference) will be cut from that sheet and pasted into another sheet.

Any help apriciated.

Any questions please ask.

Thank you

try:

=LEFT(B4,1)&MID(B4,1,1)&LEFT(C4,1)&"_"&text(rows($1:1),"0000")

Although the first part doesn't make sense (I assume a fudged example rather than an 'issue'?)
hth

Won't sorting distort the numbering?

Sorting does distort the numbering. I will need to be able to sort the data with the auto filter.

Each reference needs to be fixed to the entry not the row. Basically if row 3 has '0001' but is moved to become row 5 it should still show '0001' rather than '0003' which it will currently if moved. - if that makes sense??

But this is essentially exactly what I need so if we can overcome the sorting/moving issue then perfect!

You could just use that formula and copy & paste as values over the top.

I can't think of a suitable alternative (VBA is over the top)

But this is an ideas factory, so someone may yet come to your aid with something more elegant...

Range("K7") = Int((9999 - 0 + 1) * Rnd + 9999)

Will create duplicates.

Something more like this:

Please Login or Register  to view this content.

but it's impossible to test without a sample upload