Combine multiple data sets into one master spreadsheet

Hi,

I have an workbook I use for weekly invoices where each invoice is in its own worksheet. My goal is to be able to easily make a summary sheet at the end of the month to where I can see what the charges were for each item by invoice.

The trouble comes in that each invoice doesn't necessarily have the same charges on them and each line item has a category & a subcategory as well as the value

I've attached an example spreadsheet. The first worksheet is what I would like the combined result to look like and the other two worksheets are example invoices.

Is there an easy way to do this? The real invoices are much larger and are a pain to combine by hand which is what I've been doing.

Thanks!

Perhaps with the help of a macro like this?

To test run the macro called "Add_Invoice"

Beware that the sheet name "Summary" is hard coded and must be placed first of all the sheets. The name of the invoice sheets does not matter nor the number of them as the macro loop through them all and adds their names in cells C1, D1, E1 and so forth.

The only thing I'm a bit unsure of is the macro code for setting the format values as “$12,00” It works ok for me in Sweden but sometimes excel workbooks doesn’t travel so well even if they are supposed to do so.

Alf

That macro look promising but one problem I had with it is that in reality the "sub-category" I use for each invoice is numeric, not text, and the macro looked like it adds those numbers together. For instance if the category/subcategory was Food/12345 on two different invoices then the summary sheet would show Food/24690. I tried formatting those cells as Text on each invoice sheet but they still get added together. This also affects the totals for each row because it added in the account numbers with the charges.

I also tried out that RDB add-in but it doesn't look like it will help too much. It looks like it would put all of the invoices into one sheet and would require a lot of work to format the data the way I need to.

That macro look promising but one problem I had with it is that in reality the "sub-category" I use for each invoice is numeric, not text,

This is why one should always upload a sample file that is a copy of the real file as solutions will be based on that.

You got what you asked for, to bad it’s not what you wanted.

Alf

The actual file contained confidential information that I did not want free to grab on the internet. I tried to use an example that was equivalent to what I use for the invoices (two text fields and a number field) but obviously should've specified that one of the text fields was numeric.

I appreciate you writing up the code but I didn't ask or expect anyone to do that for me. My problem seemed like something Excel might be able to do natively without a macro, but apparently it is not.

Thank you for the help though.