Excel Formula...Complex...Invoice tracking without inputting info into multiple spreadsheets

Question

I have a list of store# in one worksheet(1) and another worksheet which has (2)invoice tracking for these stores. As the store# & invoice # are added to one worksheet(2), I want the invoice number to insert into worksheet (1) which has a the list of store# next to the correct store number. So, if I input Inv. 3124 (column1) for Store 4545 (column2) into the worksheet (2), I would like it to find store 4545 in worksheet1 and put the invoice # next to that corresponding store.

I am trying to find an easy way to track invoicing by the store number...ensuring each store is only billed once but I want to minimize the amount of time I am spending inputting information into an spreadsheets.

Thanks!

Answer 1

Sounds like a job for pivot table!¹

So if you have your data in a table you can use a pivot table. I'll assume for this exercise that your table looks like this -

If you highlight the table and go to insert - pivot table so that the range is the table and it goes to a new worksheet, you can set up a pivot table.

Here are two examples using different metrics (pictured)-

The amount of each invoice for each store, you can see if any invoices are listed twice or charged to two stores: link to bigger view of picture

Here is matching the store to the invoice and counting how many times that invoice appears for the store. It also gives you a total count of invoices per store: link to bigger view of picture

You can do any number of configurations with the pivot table, take a look at all the reference material from Microsoft.

¹this is another installment of the "sounds like a job for pivot table" series