Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create next number in sequence to be used on 2 workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create next number in sequence to be used on 2 workbooks
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 In article , Peter wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create next number in sequence to be used on 2 workbooks
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create next number in sequence to be used on 2 workbo
Thanks JE,
Checked out the link - very informative :) "JE McGimpsey" wrote: 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 In article , Peter wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create next number in sequence to be used on 2 workbo
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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to create sequence of numbers | Excel Programming | |||
create a macro that compares two workbooks | Excel Discussion (Misc queries) | |||
Macro to Create Workbooks & Worksheets | Excel Programming | |||
can't create excel add-in macro to use with other workbooks | Excel Programming | |||
Opening workbooks in sequence | Excel Programming |