Pivot tables

Resolved · Low Priority · Version 2010

Fri 7 Mar 2014, 15:55 Delegate Claire said...

Claire has attended:
Excel VBA Intro Intermediate course
Excel Advanced course

Pivot tables

I use Pivot tables already, but I can't automate the last function I need to perform.
If I have two pivot tables (one of internal data and one is external data), they are copied one under the other and I need to find differences between the two, but if I click on a cell in the one macro and subtract from a cell in the other macro, it will return a value, but if I autofill a section from this formula,it doesn't work. The only way the formula will work is if I actually type the cell reference i.e A1-A17, then this can be autofilled, but problem is the pivot table may not have same lines of data everytime. Is there a way I can fix this with VBA or another method?

Mon 10 Mar 2014, 10:49 Trainer Gary said...

RE: Pivot tables

Hi Claire

Thanks for getting in touch. This is a piece of PivotTable functionality by design. It's invoking a process called "GetPivotData" - if you look at the formula that's created you'll see that function.

You should try creating another formula but with GetPivotData turned off. To do this, select any cell in the PivotTable. On the ribbon, under the Options menu, click the drop-down arrow for Options, and click the Generate GetPivotData command to turn the feature off or on.

Try building the formula again and you should have a formula you can fill or drag.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 17 Mar 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Formst Excel to display leading zeroes

Select the cells that you want to have displaying leading zeroes.

From the 'Format' menu select 'Cells' (Or Right-click on the selected range and and select 'Format Cells').

In the 'Number' tab click on 'Custom' in the 'Category' window.

In the 'Type:' box enter zeroes that correspond to the size of the required number (eg 5 zeroes).

Click OK.

When you enter numbers into these cells, leading zeroes will be displayed.

eg 123 = 00123

View all Excel hints and tips