Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto populate an invoice from data in an existing worksheet
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 ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=561345 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto populate an invoice from data in an existing worksheet
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto populate an invoice from data in an existing works
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto populate an invoice from data in an existing worksheet
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 Wrote: 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 -- 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto populate an invoice from data in an existing worksheet
"juliebythesea" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Master worksheet automatically enters data into sub worksheets | Excel Discussion (Misc queries) | |||
how do I populate a cell with data from another worksheet, | Excel Discussion (Misc queries) | |||
VB copde help - please! | Excel Discussion (Misc queries) |