Creating Invoices using data from a spreadsheet

[tjcusick]

My wife's at home job requires that we fill out a spreadsheet with Names of Individuals, County Information and dates along with Vendor name and Address.

What I would like to do is using the information on the spreadsheet I want to be able to auto generate invoices from that information.

The table is setup as follows:

From	To	Name	Date Rcvd	County	Date Issued	Vendor Name & Address	How Many	Voids	Cost
1	9	John Smith	9/28/2013	18	9/30/2013	George Jones 123 Main St	10	0	$60
10	14	Arthur Johns	9/29/2013	41	9/30/2013	Peter Paul 321 S. Main St	5	0	$30
15	24	Paul Fellow	9/29/2013	40	9/30/2013	George Jones 123 Main St	10	0	$60

<tbody>
</tbody>

And what i would like to creates is something like

Michelle
SomePlace
SomeWhere
		INVOICE
		For: George Jones
	Name	Date	County		Amount		Cost
	John Smith	9/28/2013	18		10		$60
	Paul Fellow	9/29/2013	40		10		$60
							$120

<tbody>
</tbody>

Any suggestions on how to start to make this and have it be dynamic so that when ever another Individual is added it would also be added to the correct invoice sheet?

 

[Chris Mack]

Hi

Assuming you've already got invoices for each Vendor set up, I would have an invisible list of reference values starting in cell A6 (you make them invisible by formatting them with the custom format ";;;").

I would put a zero in A6, and then the following formula in A7:

=IF(A6="","",IF(MIN(IF(Table!$G$2:$G$100=$C$5,IF(ROW(Table!$G$2:$G$100)>A6,ROW(Table!$G$2:$G$100))))=0,"",MIN(IF(Table!$G$2:$G$100=$C$5,IF(ROW(Table!$G$2:$G$100)>A6,ROW(Table!$G$2:$G$100))))))

Entered with CTRL+SHIFT+ENTER.

You can then use this number to fill out the invoice details using INDIRECT and ADDRESS. For example, in B7:

=IF($A7="","",INDIRECT("Table!"&ADDRESS($A7,3)))

The "3" means column C, i.e. the 3rd column. So, in C7, you would have:

=IF($A7="","",INDIRECT("Table!"&ADDRESS($A7,4)))

Once row 7 has been populated with these formulas, you can simply copy them down as far as you need to, i.e. to cover the number of invoices lines you need.

Also, if you were to have just one invoice template like this, you could simply change the Vendor in C5, and the invoice would automatically adjust for that Vendor.

The invoices lines will be in the same order that they are on your original data sheet. You can sort the data sheet to adjust this, e.g., to sort the lines by Name, Date Received, Country, etc.

Hope this helps (if you have any questions let me know),

Chris.

 

[tjcusick]

To answer your first question no i do not have the invoices setup for any of them i have been doing the invoices all by hand up to this point.

I guess i probably should have also said that the report we have to submit, splits into multiple other reporting sheets...
usually we have 2 sheets some times 3. Each sheet can have up to 15 records on it.

I guess what i need is a macro or something that will go through each sheet and pull all the information for all Individuals that are linked to a specific Vendor...

 

[Chris Mack]

> To answer your first question no i do not have the invoices setup for any of them i have been doing the invoices all by hand up to this point.

That's not a problem. As I mentioned, you can just change the Vendor name on the template.

> I guess i probably should have also said that the report we have to submit, splits into multiple other reporting sheets...
usually we have 2 sheets some times 3. Each sheet can have up to 15 records on it.

In this case I would set up 3 templates, with A6 on the second template pulling through the value from A21 on the first one, etc. This would result in a continuation between the sheets.

Would that work?

Chris.

 

[tjcusick]

I'm going to try and reopen this question...
i have a 5 page spreadsheet.
Pages 2,3 & 4 have the data on them.
Contract number, Name, date, director's name and address, how many copies and total cost.

What i would like to be able to do, and this might be best done with a Macro but i'm not sure, is to be able to scan through pages 2,3 &4 to find director's Name and then pull corresponding information from those rows and place them onto another spreadsheet in order to make an invoice.

Any Ideas?