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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
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
 
juliebythesea said:
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.
Click to expand...
 
VBA Noob said:
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
Click to expand...
 
VBA Noob said:
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
Click to expand...
 
juliebythesea said:
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
Click to expand...
 
Back
Top