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

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

Hi again,

I did this simple sheet to give you a clearer idea

http://cjoint.com/?hoix2CYurg

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

"VBA Noob" wrote:

>
> Hi again,
>
> I did this simple sheet to give you a clearer idea
>
> http://cjoint.com/?hoix2CYurg
>
> VBA noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561345
>
>

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

Originally Posted by juliebythesea

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" wrote:

>
> Hi again,
>
> I did this simple sheet to give you a clearer idea
>
> http://cjoint.com/?hoix2CYurg
>
> VBA noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561345
>
>