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

what to use formula or macro ???

Submitted by kirra on 8 November, 2012 - 04:03

I have a spreadsheet where each tab is a new invoice and a second spreadsheet where sheet 1 summarises all invoices (Invoice No / Date / Item / Price)

I'd like to know if there is any formula (maybe vlookup) that I can use to bring the information from each invoice automatically to my spreedsheet 2

Or should I use macro codes ??

Thank you !!!

‹ Create macro upon double click cell display filter in new sheet My functions appear to work fine, but I get a #value error after execution ›
»

formula or macro ??

Submitted by kirra on 8 November, 2012 - 07:29.

Thanks for the reply.

However, what I need is a way to bring each item sold in the invoice to different rows in the "summary sheet". For example:

Spredsheet with all invoices:
Sheet 1 Sheet 2
Invoice 101 Invoice 102
John Smith Mary Anne
2 red pens $10 5 erasers $20
4 pencils $15 2 red pens $10

"Summary Spreadsheet":
Invoice Customer Qty Item Price
101 John Smith 2 red pen $10
101 John Smith 4 pencils $15
102 Mary Anne 5 erasers $20
102 Mary Anne 2 red pens $10

Thanks.

»
Almir's picture

formula or macro ??

Submitted by Almir on 8 November, 2012 - 07:55.

In that case, use "Consolidation" option. On summary sheet use option "Consolidation". Select each invoice as separate range. Use Top row and Left Column. Finally, use Sum Function. Prerequisite: all invoices are structured in the same way.

»
Almir's picture

what to use formula or macro ???

Submitted by Almir on 8 November, 2012 - 06:23.

I recommend using 3D formula. In Summary spreadsheet create sum of cells from all the sheets from source spreadsheet. For example, for sum of all A1 cells: in summary sheet in cell A1 type sum(now, select all sheets with invoices and select A1 at any of invoices), close the bracket and press ENTER. You will get sum of A1 cells from all invoices. Drag formula down and rihgt as necessary.

»