I thought I had posted this question before but apparently not. This is my general question..
I have a number of files I need to put together.
1. File A - A QuickBooks file (updated every month or so). This has to be cleaned in PQ and then loaded into Excel
2. File B - a regular Excel file (updated every month or so). This needs to be linked to File A via a vlookup, then cleaned for N/A.
3. File C - Pulls a combination of names/amounts/subscription types from File A and File B. This also has to have a daily date added in (unpivoted via PQ, additional formulas added in then and loaded via Connection into PowerPivot
4. Pivot Table built on this - linked to a parameter query for a date (call this Cutoff) which the user should just be able to enter.
I am trying to think of the best way to set this up for the user..this is my current thinking>
Option A
Everything in one file. Every month, dump original QuickBooks file with updated data, ditto for File B and hit Refresh. Will this update everything? Then user puts in cut off date and refreshes pivot table.
Am I missing something here that I should be missing. No real issues around security etc. or logging onto files. Is there a better way to do it.
Hi Anne,
Sounds fine. I would only load the QuickBooks data into the worksheet if you absolutely need it there. Otherwise leave as connection only, or load into Data Model/Power Pivot if you need it there.
Basically, only load data to the worksheet if you have to so you can keep your file size as small as possible.
Mynda
