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

P

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
 
G

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
 
P

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

Gary''s Student said:
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


Peter said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top