I want to automatically sort my sheets out in a workbook,
eg
sheet 1 is named "Msowdon - 101/1" - the first entry
sheet 2 "Msowdon - 101/4" - the fourth entry
sheet 3 "msowdon - 101/10" - the tenth entry
sheet 4 "Msowdon - 101/12" - the twelth entry

if i add a new sheet for the 11th job in this area the
sheet will be called "Msowdon - 101/11" and should come
after 101/10 and before 101/12, but after 101/4.

The reference 101/11 is found from an entry on the sheet,
and i can get excel to check the sheet reference and sort
the sheets back in to order. My problem is this...

Can I get excel to sort out in respect to the second
number, so 101/4 will come before 101/11?

I have tried changing the cells catagory in the format
section, and it's not possible to have a zero in front of
the 4 ie 101/04 as it is part of a much larger system.


if anyone has encountered this, or has any solutions i
would be eternally grateful.

TIA

Jon

Not using built in sorting or comparison.

"101/10" is < "101/4"

? "101/10" < "101/4"
True


So you would have to sort by name, then for sheets with the same area, break
out the numbers after the / and re sort just the area based on that.

Or you could write similar type logic for the basic sort.

--
Regards,
Tom Ogilvy

"jon" wrote in message
...
I want to automatically sort my sheets out in a workbook,
eg
sheet 1 is named "Msowdon - 101/1" - the first entry
sheet 2 "Msowdon - 101/4" - the fourth entry
sheet 3 "msowdon - 101/10" - the tenth entry
sheet 4 "Msowdon - 101/12" - the twelth entry

if i add a new sheet for the 11th job in this area the
sheet will be called "Msowdon - 101/11" and should come
after 101/10 and before 101/12, but after 101/4.

The reference 101/11 is found from an entry on the sheet,
and i can get excel to check the sheet reference and sort
the sheets back in to order. My problem is this...

Can I get excel to sort out in respect to the second
number, so 101/4 will come before 101/11?

I have tried changing the cells catagory in the format
section, and it's not possible to have a zero in front of
the 4 ie 101/04 as it is part of a much larger system.


if anyone has encountered this, or has any solutions i
would be eternally grateful.

TIA

Jon

jon wrote:

I want to automatically sort my sheets out in a workbook,
eg
sheet 1 is named "Msowdon - 101/1" - the first entry
sheet 2 "Msowdon - 101/4" - the fourth entry
sheet 3 "msowdon - 101/10" - the tenth entry
sheet 4 "Msowdon - 101/12" - the twelth entry

if i add a new sheet for the 11th job in this area the
sheet will be called "Msowdon - 101/11" and should come
after 101/10 and before 101/12, but after 101/4.

The reference 101/11 is found from an entry on the sheet,
and i can get excel to check the sheet reference and sort
the sheets back in to order. My problem is this...

Can I get excel to sort out in respect to the second
number, so 101/4 will come before 101/11?

I have tried changing the cells catagory in the format
section, and it's not possible to have a zero in front of
the 4 ie 101/04 as it is part of a much larger system.


if anyone has encountered this, or has any solutions i
would be eternally grateful.

TIA

Jon

Read this: http://www.cpearson.com/excel/sortws.htm

You will have to change the lines that read:

If UCase(Worksheets(N).Name) UCase(Worksheets(M).Name) Then

to something like this:

If val(mid(Worksheets(N).Name,4)) val(mid(Worksheets(M).Name,4) Then

The mid(...,4) is assuming the first part is always 4 chars long: "101/"
And the val(...) is assuming you are always using numbers in the second
part.

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.