Reference Number

[georgeeasten]

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

[Cheeky Charlie]

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

[royUK]

Won't sorting distort the numbering?

[georgeeasten]

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!

[Cheeky Charlie]

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...

[georgeeasten]

Thanks again Charlie but I really would like to get something a little 'more elegant'!! ;-)

Would anyone like to take up the challenge for something in VBA. Possibly something run from a button so I can select the cell or row containing that cell to populate it with the reference?!?!!?

The formula I have which produces the reference from various selections of data from various cells within the given row is as follows:

="PJW/"&G3&"/"&LEFT(B3,2)&LEFT(C3,2)&TEXT(ROWS($1:1),"0000")

It produces the following: PJW/GAFE/BiTh0001

The first part will always be PJW the second is the initials of the person dealing with that work (taken from column G) and the rest is the first two letters of two relevant entries with the unique number at the end.

A bit messy but it works!

Thanks again Charlie (BTW I already give to Action Aid! ;-) )

[georgeeasten]

I have now solved the problem (I'm on a roll today!!!).

I have two parts to creating the reference and this is the only way I can think of solving it. If someone else has any comments, I welcome them.

I have this in the cell in which the reference is to be held and indeed each cell in column K

="PJW/"&G5&"/"&LEFT(B5,2)&LEFT(C5,2)&Data!$K$7

I have this attached to a button marked 'create reference'

Sub CreateRef()

ActiveWorkbook.Save

Application.ScreenUpdating = False

    Sheets("Data").Select
    Range("K7").Select
    Range("K7") = Int((9999 - 0 + 1) * Rnd + 9999)
    
    Sheets("Live Files").Select
    ActiveCell.Select
    Selection.Copy

    Sheets("Live Files").Select
    ActiveCell.Select
    Selection.PasteSpecial xlValues
    
Application.ScreenUpdating = True
    
End Sub

Because of the order everything happens a new random number is created each time and the random number is preserved once the copy and paste special is executed!

What is produced is PJW/GAFE/AZGr15794

PJW being the manager, GAFE the case handler, AZGr two letters from two relevant cells and then the random number.

I have to say I'm quite pleased with this!

Thank you to everyone that helped me and as I said any comments are welcome.

[Cheeky Charlie]

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

Will create duplicates.

Something more like this:

Sub CreateRef()

ActiveWorkbook.Save

Application.ScreenUpdating = False

    with Sheets("Data")
.Range("K7") = application.worksheetfunction.max(.range("K:K"))+1
end with
    Sheets("Live Files").ActiveCell = Sheets("Live Files").ActiveCell 
Application.ScreenUpdating = True
    
End Sub

but it's impossible to test without a sample upload

[georgeeasten]

It works!

I would attach a copy but it is now 2mb so it exceeds the upload limit.

But thanks again Charlie.

Muchos respect