Home » Forums » Open Source Learning Center » Question and Answer

Spreadsheet help please

Submitted by kirra on 14 November, 2012 - 02:38

Need help quick please !!!!

I have a file where I save all my invoices (new one are added as sales happen) an dI have a spreadsheet where sheet 2 summarises the sales in lines, for example:

Invoice 101
Date 14/11/2012
Cusomer John Smith
01 blue pen $1.00
02 erasers $3.00

Spreadsheet (summary):
Invoice Date Customer Qty Description Price
101 14/11/12 John Smith 1 blue pen $1.00
101 14/11/12 John Smith 2 erasers $3.00

I need to find a way to bring the data from each individual invoice to the spreadsheet automatically. What should I use, formulas or VBA ???

If VBA, I need help with how to write it as I'm new in VBA.
If formula, I'd appreciate details of the formula.

Hope someone help me asap.

Thank you.

‹ How to Insert a Cell Name into the Cell Deleting data from Sheet 1 Column 1 also deleting data from Column 1 Sheet 2 and corrosponding rows on Sheet 2 ›
»

RE: Spreadsheet help please

Submitted by manny on 16 November, 2012 - 11:27.

Hi,

Can you attach an example file or provide more details, please?

I need to know how you collect your invoices, for example each invoice is in separate sheet or otherwise...

 

Best regards.

»
Nick's picture

if your invoice numbers are

Submitted by Nick on 14 November, 2012 - 08:25.

if your invoice numbers are unique, you can use VLOOKUP

 

 take a look at these:

 

13. Excel Tips - VLOOKUP

20. Excel Tips - VLOOKUP limitations - can use INDEX + MATCH instead

 

»

vlookup

Submitted by kirra on 15 November, 2012 - 04:53.

Thanks Nick for your reply.

However, I don't know if vlookup would work in this casa as 1 invoice usually contains more than 1 item

I'd look for the invoice number in a folder and how would it bring different items sold in different rows ????

Thanks.

»
Nick's picture

ok... got it now... yes you

Submitted by Nick on 15 November, 2012 - 08:15.

ok... got it now...

yes you need VBA... but more fundamentally than that, the invoice itself should not be the source of the data - it should be derived from a source.

We have a free invoice tracking utility:
http://excelexperts.com/invoice-tracking-utility

take a look

»

Thanks Nick, But again, how

Submitted by kirra on 16 November, 2012 - 01:44.

Thanks Nick,

But again, how can I bring different items sold into a source spreadsheet???? The example you sent me just consolidate the total sold in an invoice.

Also, can you help me with the VBA code. I'm new at VBA.

Thanks again.

»
Nick's picture

here are some VBA tutorials:

Submitted by Nick on 16 November, 2012 - 11:30.

here are some VBA tutorials: http://excelexperts.com/Free-VBA-Training-Videos

Happy to get one of our developers to look at your problem further, but you'll need to Request a Quote

»