How to auto populate an invoice from data in an existing worksheet

[Guest]

Excel 2003...Is it possible to create an invoice that can be linked to a
worksheet whereby selecting a record in the worksheet then activating a
custom command (macro maybe) the invoice will be populated with the data from
the record? I'm thinking along the lines of a Word mail merge type function
but the invoice must have formulas in it.

 

[VBA Noob]

Yes,

Create your invoice page in say sheet 1.

Then if your data is in sheet 2 and row 1 use the indirect function e.g


=INDIRECT("SHEET2!A"&A1)

A1 is the line in sheet 2 you want to invoice "SHEET2!A" is the sheet
name you want to indirectly look up and the A is the colun you want to
look up

Also you would have a contact sheet which you would look at the
customer address with a indirect vlookup.

The trick is to have a control box for the

Name of company or person to lookup
Name of Sheet (If referencing more than one).
Row Number


I've attached the link which might help on the indirect front.

http://www.j-walk.com/ss/excel/usertips/tip015.htm

VBA noob

 

[VBA Noob]

Hi again,

I did this simple sheet to give you a clearer idea

http://cjoint.com/?hoix2CYurg

VBA noob

 

[Guest]

Thank you. I've never used the control box, but I looked it up and looks like
it will do the trick. Question: Won't this become cumbersome with a large
customer list (say 50 to 100)? I've used data validation lists which the
control box seems similar to. What is the difference?

juliebythesea

 

[VBA Noob]

The vlookup is very quick even with around 300 plus customers.

Sorry when I refered to the control box I just meant the data you need
to drive the invoice.

I agree the use of data validation list is a good idea for Customer
names etc

The Sales tab could be by month and it will still be quick. I added a
marco to mine to send the invoice if there was a valid e-mail address
which saves time.


VBA Noob

 

[Leviatano]

Excel 2003...Is it possible to create an invoice that can be linked to a
worksheet whereby selecting a record in the worksheet then activating a
custom command (macro maybe) the invoice will be populated with the data from
the record? I'm thinking along the lines of a Word mail merge type function
but the invoice must have formulas in it.

 

[Leviatano]

Yes,

Create your invoice page in say sheet 1.

Then if your data is in sheet 2 and row 1 use the indirect function e.g


=INDIRECT("SHEET2!A"&A1)

A1 is the line in sheet 2 you want to invoice "SHEET2!A" is the sheet
name you want to indirectly look up and the A is the colun you want to
look up

Also you would have a contact sheet which you would look at the
customer address with a indirect vlookup.

The trick is to have a control box for the

Name of company or person to lookup
Name of Sheet (If referencing more than one).
Row Number


I've attached the link which might help on the indirect front.

http://www.j-walk.com/ss/excel/usertips/tip015.htm

VBA noob

 

[Leviatano]

The vlookup is very quick even with around 300 plus customers.

Sorry when I refered to the control box I just meant the data you need
to drive the invoice.

I agree the use of data validation list is a good idea for Customer
names etc

The Sales tab could be by month and it will still be quick. I added a
marco to mine to send the invoice if there was a valid e-mail address
which saves time.


VBA Noob

 

[Leviatano]

Hi again,

I did this simple sheet to give you a clearer idea

http://cjoint.com/?hoix2CYurg

VBA noob

 

[Leviatano]

Thank you. I've never used the control box, but I looked it up and looks like
it will do the trick. Question: Won't this become cumbersome with a large
customer list (say 50 to 100)? I've used data validation lists which the
control box seems similar to. What is the difference?

juliebythesea