Calculated Field for year-to-month figure

[whoiswct]

I have a raw data which will be updated each week by other department and I can only use it but I cannot edit the original. So, I call the Pivot table to connect it as external source.

The raw data consists every month revenue figure (numeric) from last year Jan to current month.
e.g. Field:
Jan2011, Feb2011, Mar2011, ... , Dec2011
Jan2012, Feb2012, Mar2012, ... , Current_Month2012
The raw data file already pre-set Jan2012 to Dec2012 fields but no figure until the month is past.

I need to calculate Sum of Year-to-month figure, so I use Calculated Field.
e.g. Now is Nov 2012 and thus I have the figure up to Oct.
Sum of year-to-month (last year) = Jan2011 + Feb2011 + ... + Oct2011
Sum of year-to-month (this year) = Jan2012 + Feb2012 + ... + Oct2012

My problem is:
I need to modified the Calculated Field every month by adding the current month field to the formula.
Any automation I can do?

[Ken Puls]

What version of Excel are you using? 2007 or 2010?

Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.

[whoiswct]

What version of Excel are you using? 2007 or 2010?

Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.

excel 2010.
attached my sample file. since the fileds is arrange in column but not in row, so it seems cannot be group as suggested.

[Ken Puls]

The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

What are you connecting to in the other workbook? A table or a PivotTable?

Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/

[Bob Phillips]

Also, how to you grab the data, an exported file, a query, ADO, or what?

[whoiswct]

The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

What are you connecting to in the other workbook? A table or a PivotTable?

Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/

Ans. I create a PT and direct connect to other excel file table.
Ans. Yes, I am pulling the data directly into a PT because I want to have latest data every time.

[whoiswct]

Also, how to you grab the data, an exported file, a query, ADO, or what?

The raw data is shared by my another department. I use PT connect as external data source for analysis only.

[Bob Phillips]

I think the simplest way will be to write to macro that transposes the data, then pivot that new data.

[Ken Puls]

We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...

Yes, I am pulling the data directly into a PT because I want to have latest data every time.

Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?

Try this:
Go to Data --> Exisiting Connections
Your PivotTable's connection should be right at the top of the list. Click it and choose Open
Now, select "Table" and click OK

At this point, you'll get an official Excel table of the source data. And this table WILL update each time you open the file. You can now build your PivotTable using that table as the source.

May not look like you've accomplished anything different here, except adding an extra step, but that's not true. You've added versatility as you can add new columns to the table, and the formulas in them will automatically apply each time the data is updated.

[whoiswct]

We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...



Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?

YES.

Thanks for your solution. It's a prefect solution for my case and working fine with me.

Something minor but want some advice. The Table seems will not auto refresh and I need to click by hand (data>refresh all) even though I restart the workbook. Is there any config I need?