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):
For tax filing my accountant wants my expenses formatted in a very particular way, like this:
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.
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):
For tax filing my accountant wants my expenses formatted in a very particular way, like this:
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:
