Conditional Usage of Data Sources in Pivot

[nondigital]

I have expense forecast for the whole fiscal year which I need to summarize via Pivot Table. Data is available in two different tables, "Forecast" and "Actual".
How to do the following:

At the start of the year, Pivot should take all data from Forecast table. However, after each month closing, the pivot should take data of the past months from Actual table and current and future months from Forecast table. This means, in the month of June, Pivot should take data up to May from Actual table and from June to December from Forecast table, thus creating the forecast model of whole fiscal year as estimated in the month of June. And this (Actual + Forecast) needs to be updated every month. (For example, if I work without Pivot, I just enter month number 5 in a cell and data is selected from Actual till month 5 and rest from Forecast.)

 

[Fazza]

Please set up the pivot table with definition like,

SELECT *
FROM Forecast
WHERE Month([name of your field with dates data]) >= Month(Date)
UNION ALL
SELECT *
FROM Acutal
WHERE Month([name of your field with dates data]) < Month(Date)

for example, save data file, open new workbook (CTRL-N), via ALT-D-P, choose external data source at first step, get data, Excel files, browse, choose the source data file, then the 'Forecast' table, etc & continue to end of wizard. Choose option to edit in MS Query and edit SQL in line with above. Specifics to suit your set up. 'Open door' icon to exit MS Query & complete the pivot table. If you like you can move the entire worksheet containing the pivot table into the source data file.

Now you just need to refresh the pivot table & it does what you've asked. Or you can set it to refresh on file open & you never need to touch it again as it will be refreshed every time you open the file.

 

[nondigital]

Thanks a lot. Will try this now.