Creating a synthethis list of data based on multiple references

[fso]

Hello, I'm basically trying to go from table 1 to table 2 below:

ITEM INVOICE NUMBER BALANCE DUE
TABLE AAA £500.00
CHAIR AAA £150.00
CUTLERY CCC £250.00
MIROR BBB £200.00
TABLECLOTH AAA £220.00
PLATES CCC £520.00
GLASSES CCC £500.00
BOOKS BBB £450.00
ARTWORK BBB £150.00
VASE AAA £250.00



INVOICE NUMBER BALANCE DUE
AAA £1,120.00
BBB £800.00
CCC £1,270.00

The table 2 will be on a different worksheet and basically I would like it to find out autmomatically the invoice numbers without me having to enter them Is this possible?

[DonkeyOte]

Simply push your Table 1 data into a Pivot Table - set INVOICE NUMBER as Row Label and BALANCE DUE as Data Field (set to sum).

If needed you can create a Dynamic Named Range for use with the Pivot - see: http://www.contextures.com/xlPivot01.html
(though the above link is not based on XL2010 (rather pre-2007) the gist is the same)

[fso]

Thanks this is starting to lead somewhere but the pivot table won't update itslef as I add new invoices down the main worksheet. I.E. if tomorrow I receive an invoice XXX for 3-4 items in the list, I want the synthetised list to update itself automatically and find that new invoice on its own without having to re-do a new pivot table every single time.

I had a look at your suggested link, thank you very much, but I can't seem to reproduce what it says / the new excel has different layouts and ways to access tools etc and I can't do what it recommends doing.

[DonkeyOte]

the pivot table won't update itslef as I add new invoices down the main worksheet.

you can use a Dynamic Named Range as source (as outlined) and in turn if really necessary you can use VBA to refresh the Pivot Cache automatically (rather than having the arduous task of clicking Refresh)

sample attached illustrating all of the above

edit:
you might also want to consider converting your Range to a Table (see Insert Tab) and using the Table as the source for the Pivot (alternative to using Dynamic Named Range)

[fso]

OK thanks got it now! not 100% sure how the dynamic named range works but I've figured it out with the pivot table function. I'll put in the macro to refresh the pivot table automatically as per your suggestion!

Thanks a lot!