Macro to create next number in sequence to be used on 2 workbooks

[Peter]

Hi all -

I have 2 workbooks (excel 2003) for tracking invoice numbers - which now run
alternate to each other - to avoid having to open both workbooks and having
to look at which was the last number used (to stop duplicate invoice number)
is it possible to create a simple macro that will look at both lists -
identifty last number used and then add 1 - putting this in the next
available cell. Hope this makes sense!

Simple layout of both workbooks:

Book 1 Book 2
A B A B
Date Invoice Number Date Invoice Number
1/10/08 12345 3/10/08 12346
6/10/08 ??? 5/10/08 12347

 

[JE McGimpsey]

An alternative would be to store the last used number in the registry or
a text file. See

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

 

[Gary''s Student]

Say Book1 and Book2 are in C:\test

In ANY workbook, the next available sequence number is one more than the max
in column B of Book1 and Book2:

=MAX(MAX(Sheet1!$B:$B),MAX('C:\test\[Book2.xls]Sheet1'!$B:$B))+1

Enter the formula OUTSIDE of column B in either ( or both) workbooks. Then
copy the cell and paste/special/value where you need it in column B

 

[Peter]

Thanks JE,

Checked out the link - very informative

 

[Peter]

Hi Gary

Thanks for this - I was not sure if a formula would capture it, I will be
able to use this formula in a simple macro - so that the girls in the office
only have to click on macro button - should work a treat.

Thanks

Say Book1 and Book2 are in C:\test

In ANY workbook, the next available sequence number is one more than the max
in column B of Book1 and Book2:

=MAX(MAX(Sheet1!$B:$B),MAX('C:\test\[Book2.xls]Sheet1'!$B:$B))+1

Enter the formula OUTSIDE of column B in either ( or both) workbooks. Then
copy the cell and paste/special/value where you need it in column B
--
Gary''s Student - gsnu200810

Hi all -

I have 2 workbooks (excel 2003) for tracking invoice numbers - which now run
alternate to each other - to avoid having to open both workbooks and having
to look at which was the last number used (to stop duplicate invoice number)
is it possible to create a simple macro that will look at both lists -
identifty last number used and then add 1 - putting this in the next
available cell. Hope this makes sense!

Simple layout of both workbooks:

Book 1 Book 2
A B A B
Date Invoice Number Date Invoice Number
1/10/08 12345 3/10/08 12346
6/10/08 ??? 5/10/08 12347