Sequential Numbering

I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

Try this:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")

HTH
--
AP

"Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
45A8FE12-9102-4ED4-AE32-BA5A3CE1BECA@microsoft.com...
>I have a two part number for tracking delivery notes, separated by a slash
> and each number increasing sequentially eg 8987/010942, 8988/010943 etc.
> How
> can I do this automatically? Also Invoice Numbers eg ms/0988/06,
> ms/0989/06
> etc.
>
> Thanks.
>
>

Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
should've maybe mentioned the numbers go down in a column? I'm a relatively
new user so appreciate your help!

"Ardus Petus" wrote:

> Try this:
> =LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
>
> HTH
> --
> AP
>
> "Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
> 45A8FE12-9102-4ED4-AE32-BA5A3CE1BECA@microsoft.com...
> >I have a two part number for tracking delivery notes, separated by a slash
> > and each number increasing sequentially eg 8987/010942, 8988/010943 etc.
> > How
> > can I do this automatically? Also Invoice Numbers eg ms/0988/06,
> > ms/0989/06
> > etc.
> >
> > Thanks.
> >
> >
>
>
>

Enter your initial value in A1:
8987/010942
Enter my formula in A2:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
You should get the expected result of:
8988/010943
You can now drag down my formula to get the following sequence numbers.

If your initial value is no t in A1 but in any other cell, just replace A1
with that other cell's address in my formula.

HTH
--
AP

"Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
78893697-80DA-4638-B9E9-BE5E88CFD23A@microsoft.com...
> Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
> should've maybe mentioned the numbers go down in a column? I'm a
> relatively
> new user so appreciate your help!
>
> "Ardus Petus" wrote:
>
>> Try this:
>> =LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
>>
>> HTH
>> --
>> AP
>>
>> "Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
>> 45A8FE12-9102-4ED4-AE32-BA5A3CE1BECA@microsoft.com...
>> >I have a two part number for tracking delivery notes, separated by a
>> >slash
>> > and each number increasing sequentially eg 8987/010942, 8988/010943
>> > etc.
>> > How
>> > can I do this automatically? Also Invoice Numbers eg ms/0988/06,
>> > ms/0989/06
>> > etc.
>> >
>> > Thanks.
>> >
>> >
>>
>>
>>

Without using too complicated formulas:

Insert a column for the first part of the number ( e.g. column A) and a
column for the second part of your number ( e.g. Column B) and in the third
column use the formula =A1&"/"&B1

Sequential numbering can the be arranged in colums A and B

Hans

"Mel" wrote:

> I have a two part number for tracking delivery notes, separated by a slash
> and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
> can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
> etc.
>
> Thanks.
>
>

et voila! Thanks very much, now to understand the formula!

"Ardus Petus" wrote:

> Enter your initial value in A1:
> 8987/010942
> Enter my formula in A2:
> =LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
> You should get the expected result of:
> 8988/010943
> You can now drag down my formula to get the following sequence numbers.
>
> If your initial value is no t in A1 but in any other cell, just replace A1
> with that other cell's address in my formula.
>
> HTH
> --
> AP
>
> "Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
> 78893697-80DA-4638-B9E9-BE5E88CFD23A@microsoft.com...
> > Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
> > should've maybe mentioned the numbers go down in a column? I'm a
> > relatively
> > new user so appreciate your help!
> >
> > "Ardus Petus" wrote:
> >
> >> Try this:
> >> =LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
> >>
> >> HTH
> >> --
> >> AP
> >>
> >> "Mel" <Mel@discussions.microsoft.com> a écrit dans le message de news:
> >> 45A8FE12-9102-4ED4-AE32-BA5A3CE1BECA@microsoft.com...
> >> >I have a two part number for tracking delivery notes, separated by a
> >> >slash
> >> > and each number increasing sequentially eg 8987/010942, 8988/010943
> >> > etc.
> >> > How
> >> > can I do this automatically? Also Invoice Numbers eg ms/0988/06,
> >> > ms/0989/06
> >> > etc.
> >> >
> >> > Thanks.
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Thanks - but that doesn't seem to work, says there's an error in the formula?


"hans bal(nl)" wrote:

> Without using too complicated formulas:
>
> Insert a column for the first part of the number ( e.g. column A) and a
> column for the second part of your number ( e.g. Column B) and in the third
> column use the formula =A1&"/"&B1
>
> Sequential numbering can the be arranged in colums A and B
>
> Hans
>
> "Mel" wrote:
>
> > I have a two part number for tracking delivery notes, separated by a slash
> > and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
> > can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
> > etc.
> >
> > Thanks.
> >
> >

=A1&"/"&B1 works in my Excel, but you can also try : =concatenate(A1;"/";B1)
( ; is the list separator, depending on your regional settings you ay have
to replace it by a , )




"Mel" wrote:

> Thanks - but that doesn't seem to work, says there's an error in the formula?
>
>
> "hans bal(nl)" wrote:
>
> > Without using too complicated formulas:
> >
> > Insert a column for the first part of the number ( e.g. column A) and a
> > column for the second part of your number ( e.g. Column B) and in the third
> > column use the formula =A1&"/"&B1
> >
> > Sequential numbering can the be arranged in colums A and B
> >
> > Hans
> >
> > "Mel" wrote:
> >
> > > I have a two part number for tracking delivery notes, separated by a slash
> > > and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
> > > can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
> > > etc.
> > >
> > > Thanks.
> > >
> > >

Hi - ok that worked (with the ,) but when I drag down it fills the cells with
the same number?

"hans bal(nl)" wrote:

> =A1&"/"&B1 works in my Excel, but you can also try : =concatenate(A1;"/";B1)
> ( ; is the list separator, depending on your regional settings you ay have
> to replace it by a , )
>
>
>
>
> "Mel" wrote:
>
> > Thanks - but that doesn't seem to work, says there's an error in the formula?
> >
> >
> > "hans bal(nl)" wrote:
> >
> > > Without using too complicated formulas:
> > >
> > > Insert a column for the first part of the number ( e.g. column A) and a
> > > column for the second part of your number ( e.g. Column B) and in the third
> > > column use the formula =A1&"/"&B1
> > >
> > > Sequential numbering can the be arranged in colums A and B
> > >
> > > Hans
> > >
> > > "Mel" wrote:
> > >
> > > > I have a two part number for tracking delivery notes, separated by a slash
> > > > and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
> > > > can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
> > > > etc.
> > > >
> > > > Thanks.
> > > >
> > > >

Set calculations to automatic: Tools/Options/Calculation

In article <0D81A904-6692-404C-A087-FA6D492D5B97@microsoft.com>,
Mel <Mel@discussions.microsoft.com> wrote:

> Hi - ok that worked (with the ,) but when I drag down it fills the cells with
> the same number?

Thanks, but calculations set to automatic already... must be doing something
wrong?

"JE McGimpsey" wrote:

> Set calculations to automatic: Tools/Options/Calculation
>
> In article <0D81A904-6692-404C-A087-FA6D492D5B97@microsoft.com>,
> Mel <Mel@discussions.microsoft.com> wrote:
>
> > Hi - ok that worked (with the ,) but when I drag down it fills the cells with
> > the same number?
>