How to extract a subset of records into a another sheet in the same file?

niblettes

New Member
Joined
May 22, 2013
Messages
3
I've used Excel off and on for several years, and I'm frustrated that I just can't figure out how to accomplish what seems like a very simple task. Let me explain...

I have a single sheet where I register all financial transactions for my business (revenue, expenses, everything). I have a set of other sheets within the same file to run reports on the register's data, like Profit & Loss Statement, Balance Sheet, Bank Deposits, etc... All of the reports are just pivot tables that summarize some set of data from the register.

This is what the register sheet looks like (with dummy data):

spreadsheet_is.png


For tax filing my accountant wants my expenses formatted in a very particular way, like this:

spreadsheet_wants.png


She wants a sheet per month, containing a table per currency (I do business in us US and CA dollars) with columns for each of the expense categories the tax forms require (ie, Subcontractors, Meals, Software, Travel, Other, etc...). She wants each individual expense amount extracted from the register sheet and recorded on the appropriate month sheet, for the appropriate currency table, under the appropriate column, and she wants the description for each transaction in the "description" column.

So the filtering criteria are "month", "native currency" and "category." To make things a little more complicated the "Other" column should get all those expenses not sorted under any of the other columns.

This may not be trivial, but I'm sure its relatively easy -- my brain just can't figure it out. Any tips?

PS.
Not sure why the image links aren't showing properly, but if you copy-paste the URL you can see them.
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top