Sort-Excel-Custom Cell

[wolf.stalker]

hi all and thanks for any help you can provide. let me get to the meat of it...

here is a sample of what my data STARTS off looking like..this is what an end user types in (it was thought this was qucker than typing in XXX-XXXX-XXX)

313456313457313458

now each cell is formatted like XXX-XXXX-XXX where the first three digits are actually put in place by an end user (they are store numbers). the next 4 is the date and the last 3 are the po#. so, the info above would look like this for store #123.

123-0313-456
123-0313-457
123-0313-458

this is exactly what i want/need. the problem lies in that when i want to sort, it sorts by the last 3 digits. i need to sort by the 1st three :-)

the other prolem i have is that this info is linked on a few different sheets but the end result looks like the above 123-0313-456. i have like 30 stores and each one COLULD give me up to 15 po#s....that is what i am trying to sort, except the data there is just a link..and i get something looking more like this when i try and sort it as it is sorting the last 3 digits...

010-0313-456063-0313-456010-0313-457063-0313-457010-0313-458063-0313-458010-0313-459

when it should look something like this...

010-0313-456010-0313-457010-0313-458010-0313-459063-0313-456063-0313-457063-0313-458063-0313-459



any ideas?

[tstav]

It's been quite a while since you opened this thread and I can see there are no answers so far. So, I will dare to answer by asking a lot of questions...

here is a sample of what my data STARTS off looking like..this is what an end user types in (it was thought this was qucker than typing in XXX-XXXX-XXX)
313456313457313458

Does that have anything to do with the problem you're trying to share with us?

now each cell is formatted like XXX-XXXX-XXX where the first three digits are actually put in place by an end user

But didn't you just say in the previous paragraph that the end users type in something like 313456313457313458 which (if I got it right) is the date plus po#?
Why are you trying to tell us what the users are typing?
If you just told us what the cells actually contain, wouldn't that be better?

the end result looks like the above 123-0313-456

Better tell us what the actual cell value is, not what it looks like (=what it is formatted into).

the other prolem i have is that this info is linked on a few different sheets

What do you mean? What info? What different sheets?
What would you expect us to infer from that?
Why are you telling us that?

except the data there is just a link

???

010-0313-456063-0313-456010-0313-457063-0313-457010-0313-458063-0313-458010-0313-4

Where did this string come from? How can you be sorting a column of values and getting a string like this?
Unless this was not supposed to be a string and you meant
010-0313-456
063-0313-456
010-0313-457 and so on...

Please try to be more clear and I'm sure answers will start coming in...

[wolf.stalker]

yeah, that came out ALL sorts of bad and ugly! the data that got posted is all goofy. I am not at the office today, and I just logged in to see the response, but i will clean up the poo and see what i can do...

this is what the end user types in
313456
313457
313458

this is what the CELL shows in their workbooks..
010-0313-456 (010 - store num)
010-0313-457
010-0313-457

the "custom" format option looks like 010-0000-000 which will then show 010- (plus what ever the user keys in). does that make sence?

[tstav]

Honestly. Not to me. At least, not enough.
Could make sense to somebody else though... You'll see.

[RonMcK]

yeah, that came out ALL sorts of bad and ugly! the data that got posted is all goofy. I am not at the office today, and I just logged in to see the response, but i will clean up the poo and see what i can do...

this is what the end user types in
313456
313457
313458

this is what the CELL shows in their workbooks..
010-0313-456 (010 - store num)
010-0313-457
010-0313-457

the "custom" format option looks like 010-0000-000 which will then show 010- (plus what ever the user keys in). does that make sence?

NS,

I'll get brave and join the conversation.

Your custom format adds the hyphens. Where does the store number come from? Is that specific to an individual workbook and does it reside in defined cell? How does it get prepended to the users numbers (date and PO), a macro that you already have or just an Excel formula (=$A$2 & "cell with user's numbers")?

How does your format (and your users for that matter) deal with dates for October, November and December? Is 121 Jan 21 or Dec 1, for instance?

Anyway, my contribution for the moment.

Thanks,

[mdmackillop]

Formatting the cell does not include 010, 013 etc. as part of the cell value. Use a Worksheet event to modify the typed data to include the prefix.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then '<==== Change to suit
Target = "010-" & Format(Target, "0000-000")
End If
Application.EnableEvents = True
End Sub
[/VBA]