A
Andy Bailey
I'd appreciate some help to determine if I'm attempting the impossible...
We have a workbook to generate proposals. Each proposal contains numerous
sections. Each section has many line items. The first item in section 1 is
numbered 01-001, the next 01-002 etc. The 01 is the section number, the 001
is the item number. Iuse the formula:-
=CONCATENATE((TEXT($A$2,"00")),"-",(TEXT((RIGHT(OFFSET(A5,-1,0),3)+1),"000")))
to automatically generate the item number row by row. The value in $A$2 is
the section number. This way I can use vba to insert a row and the numbers
look after themselves.
If we're lucky enough to get the order I'd like to use the proposal workbook
as a basis for executing the contract. Now for the problem. Each item in the
proposal could consist of a number of separate sub items which we'd then
identify as 01-001-001, 01-001-002 etc to allow us to order all the bits.
What I'm trying to do is generate some vba to insert a row (or a number of
rows) that allows me to detail the sub items but not lose the original
numbering.
Something like this:-
Proposal
Item No. Sub-item Name
01-001 Widget A
01-002 Widget B
Now a contract - use vba to insert rows where necessary
Item No. Sub-item Name
01-001 Widget A
01-001 001 Sub part a1
01-001 002 Sub part a2
01-002 Widget B
01-002 001 Sub part b1
01-002 002 Sub part b2
01-002 003 Sub part b3
I think I've rambled enough. Any help appreciated.
Andy
We have a workbook to generate proposals. Each proposal contains numerous
sections. Each section has many line items. The first item in section 1 is
numbered 01-001, the next 01-002 etc. The 01 is the section number, the 001
is the item number. Iuse the formula:-
=CONCATENATE((TEXT($A$2,"00")),"-",(TEXT((RIGHT(OFFSET(A5,-1,0),3)+1),"000")))
to automatically generate the item number row by row. The value in $A$2 is
the section number. This way I can use vba to insert a row and the numbers
look after themselves.
If we're lucky enough to get the order I'd like to use the proposal workbook
as a basis for executing the contract. Now for the problem. Each item in the
proposal could consist of a number of separate sub items which we'd then
identify as 01-001-001, 01-001-002 etc to allow us to order all the bits.
What I'm trying to do is generate some vba to insert a row (or a number of
rows) that allows me to detail the sub items but not lose the original
numbering.
Something like this:-
Proposal
Item No. Sub-item Name
01-001 Widget A
01-002 Widget B
Now a contract - use vba to insert rows where necessary
Item No. Sub-item Name
01-001 Widget A
01-001 001 Sub part a1
01-001 002 Sub part a2
01-002 Widget B
01-002 001 Sub part b1
01-002 002 Sub part b2
01-002 003 Sub part b3
I think I've rambled enough. Any help appreciated.
Andy