Excel problem - pivot tables and multiple data sources

Excel problem - pivot tables and multiple data...

Didn't find your answer?

Hi All,

I'm trying to create a new spreadsheet for clients, but I've hit a couple of problems.

It needs to be very simple for them to complete as they really are a bit adverse to doing any of the work themselves!  I have a page for Income and a page for expenditure, each with columns for date, details, amount and category (e.g. telephone, fuel etc).

My plan was to then create a pivot table for the Income and a pivot table for the expenditure with months along the top and categories down the side and then have one row at the bottom that subtracts the expenditure table total from the income table total to get the profit.

This all worked fine except for two things:

One is that when I group the dates so they appear as months rather than each individual date it puts them in order from Jan - Dec, rather than April - March.  I cant find any way to get it to take the year into consideration so it starts with the correct month.

Second is that the profit column at the bottom does not move down if the pivot table increases in size.  You can chose to overwrite it, in which case it disappears, or not to overwrite it in which case the pivot table doesn't properly create itself.

Anyone any ideas how to sort this out?

I did try using multiple data sources to put it all in one pivot table, but I couldn't get the categories to appear properly down the side so it didn't seem to do the same thing as a normal pivot table - that might just be that I don't understand how to properly use a pivot table from multiple data sources - I did google it and watch a few online tutorials but I still couldn't really get it to do what I wanted it to do.

Any help would be much appreciated.

Many thanks

BG

Replies (7)

Please login or register to join the discussion.

avatar
By stevie
24th Apr 2014 08:55

Maybe...

...Group by year AND month. And use a separate summary sheet for your profit calculation.

Thanks (0)
avatar
By paulwakefield1
24th Apr 2014 09:14

Some ideas

The months order can be achieved by manually reordering the months. Assuming you are using a recent version of Excel, right click in the months in the pivot table and choose "Sort" and "More sort options" and check that "Sort options" are set to Manual (it probably already is). Then highlight the months Jan - Mar (just the months is fine, you don't need the whole column) and drag them to after Dec. This should give you the order you need.

You can also set up a custom list but this should not be necessary.

How about having the totals above the pivot table with the analysis below? Or on a separate sheet? In fact the whole reporting could be on a separate sheet using the GETPIVOTDATA functions.

 

Another approach which can work well especially if your Income and Exepnditure sheets are set up as Tables is to skip pivot tables and have a P&L which uses Sumif and structured references (the structured reference is not essential but is much safer and easier to read as well).

Thanks (0)
avatar
By paulwakefield1
24th Apr 2014 09:30

Actually

I think, with the structure you want, the Sumif(s) solution is going to be too messy so forget that!

Thanks (0)
By bro0010
24th Apr 2014 10:40

Multiple data sources

Hi Batty Girl,

I think your problem with PivotTables is that you may have been using [Multiple consolidation ranges] to try to create a single PivotTable with your data. However, I think you'd need to use [External data source] instead. This technique uses the ODBC driver for Excel and a Union SQL query (based on the income and expenditure pages) to create a single data source - as you wanted. I have used this for exactly your circumstance before and it has worked well. I'd be happy to configure your spreadsheets to do this for you. Once set-up you shouldn't need to know anything about how it works - you just refresh the PivotTable and it will bring in your data.

My solution to your date issue is to use calculated Fiscal Year and Fiscal Month fields within the SQL to allow the presentation you want. eg April 2014 will be in the 2014-15 fiscal year, the Fiscal Month would be Apr-14-15 the Fiscal Month Number would be 1. Again, I've found this works well for me.

For presentational purposes I generally put a static summary of the PivotTable contents on a separate page using Getpivotdata.

Another approach I've used before that may work well for you would be to put the Fiscal Year / Month calculations in your source pages and use Sumproduct to generate a static summary. This misses out the PivotTable step but doesn't provide the nice drill-to-transactions functionality that a PivotTable does. Of course you can always use data filter on the two source pages to get at the same information. The other downside of this is that, with large data sets, the calculation time for the Sumproduct formulae can become noticeable. Not so with the calculation time of the PivotTable approach - all the totals are pre-calculated when the PivotTable is refreshed.

Both approaches offer the possibility of extending the analysis options through hierarchical groupings. e.g. both [Petrol] and [Road Tax] (entered on your expenses sheet) could both be assigned to a [Motor Expenses] sub-category heading and an [Overheads] category within the overall P&L.

I hope this will give you some useful ideas but, as I said before, I'd be happy to configure your sheets to work either way if you wished.  All I'd need would be a copy of your workbook with all but a few lines of dummy data on each sheet removed. It would be helpful to have a list of your posting codes in the dummy data provided so that I could draw up a static presentation report  for those codes.

Best wishes,

Ian

Onion Reporting Software Ltd

Sage month end reporting in Excel

www.onionrs.co.uk

Thanks (0)
Simon Hurst
By Simon Hurst
24th Apr 2014 17:19

Power Query append

Should you happen to have Excel 2013 Professional Plus or Standalone, then the free Power Query addin has an append feature which may provide an interesting alternative method for combining separate tables into a single PivotTable. There is a brief example towards the end of this article:

https://www.accountingweb.co.uk/article/working-accounts-data-excel-powe...

Thanks (0)
avatar
By Richard Willis
25th Apr 2014 12:47

Use ISO dates?

I find more and more that using ISO dates makes life much easier, e.g. today = 20140425.  Used in file names, for example, they will appear in chronological order.

Thanks (0)
avatar
By Chesterlad
08th May 2014 16:48

ordering from April to March

if you add an additional column into the data sheet, you can do an IF statement or vlookup (based on the month functionality), etc that looks at the month and puts a number infront of it depending on where it needs to be,  So April 14 would be 1 April 14, May would be 2 May 14, etc.

If you had multiple accounting years you could put something like 2014-15 1 April, 2015-16 1 April, etc which would work.

 

Thanks (0)