How to fix error 1004 in Pivottable situation?

[feipezi]

Hi,

Here is what I'm doing. Please bear with me for a moment and I'll try to cut the long story short.

I set up a PT to get the percentage of certain products using the feature of '% Of Row', which means you have to have all the products in a market segment, or the percentage would be exaggerated. But the users of the application do not want to see all the products. Excel PT does not have the feature of giving you the right % with fewer products, I guess.
What I did is to leave the PT the way it is, copy part of the products I need from the PT to another location and make it a table (I call it "dummy table") and graph it. This way, I can give users the products they want and the calc of the percentage would be correct. But I have to link the dummy table to the PT behind it by a bunch of Comboboxes and Buttons.

Here is my problem. Everytime I make some change on Named Ranges, which are for the dropdowns of Comboboxes and they are on the same sheet of the dummy tab and the PT, I will get errors like '1004' or '5: Invalid procedure call...' or something else because of the worksheet events. I tried 'Application.EnableEvents=False' and others, they won't work.

Or I can probably move the Named Ranges away from the Sheet where the PT and the dummy tab are located.

What would you think? Any better way of doing it: keep the dummy, PT, and the Named Ranges on the same sheet without triggering errors?

Thanks in advance.

 

[SkipVought]

hi,

I would not mess with linking to the PT.

Do you aggregations referencing the source data directly.

Your average, BTW, will be a SUMIFS()/(FIXED COUNT)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

I wouldn't like to mess with linking the PT if I had a choice.

I just copy the PT data (DataBodyRange) to somewhere else in order to graph it.

I can understand you'd like to use Excel funcs instead of relying on VBA. But funcs are not versatile enough to handle the situation I'm in. That's what I believe and that's why I have to set up VBA macros. But I will try what you suggested see what's going to happen. I'll keep you posted.

Thanks.

 

[SkipVought]

I just copy the PT data (DataBodyRange) to somewhere else in order to graph it.

WHY? You can chart right from the PT!

Functions can and will handle an average based on a fixed number of points or a calculated number of points.

If you have 2007+ you can use AVERAGEIFS(), or SUMIFS() and COUNTIFS(), or if 97-2003 use SUMPRODUCT() to sum and count

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I'd have thought functions could do far more than a pivot table. No one said not to use in conjunction with VBA. However, if the pivottable is giving you the results you want but you want to only display some of them then you could simply hide rows or columns. Similarly you can copy just the visible cells. Or use GetPivotData.

Gavin

 

[SkipVought]

FYI, I often start out solving an aggregation issue by using a PT and on some occasions I switch to formulas to create my own pivot as the situation warrants. I can control the customization and appearance to a far greater extent.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Thank you all for replying.

But life is not that easy for me. I have at 7 reports to update each month. Some of the data sheets have more than 60,000 rows. Each data tab has 7 to 8 hierarchical key variables. It's going to be a Hell of job setting up formulas.

Don't get me wrong. I like functions too. But for my job, if I use aggregation funcs, I guess I have to use COUNT() or COUNTA() to set up criteria. It would be much easier to use PTs to handle the situation. One thing that I don't like funcs is they cannot handle DO LOOPs or FOR loops.

Hiding rows or columns? I'd have done that if it was feasible. I need a few of the columns from each of 3 PTs. And the column position is dynamically changing. You have to seach ColumnRange for the products you want to hide or unhide. It's not like you can hide Columns("F") or Columns("W") each time the PT is updated. I actually tried that before. Even you made it hiding columns in PT, the PT Chart will still be showing, which won't happen in a regular chart.

Thanks.

 

[Gavona]

Hiding rows or columns? I'd have done that if it was feasible. I need a few of the columns from each of 3 PTs. And the column position is dynamically changing. You have to seach ColumnRange for the products you want to hide or unhide. It's not like you can hide Columns("F") or Columns("W") each time the PT is updated. I actually tried that before. Even you made it hiding columns in PT, the PT Chart will still be showing, which won't happen in a regular chart.

In built Excel functions tend to be much quicker than VBA code with loops.

No one said not to use in conjunction with VBA.

The huge advantage of Pivots is that they include all the data, are quick to develop and tweak and help you to understand the issue. I agree with Skip that having developed a (partial) solution with them switching to formulae/functions can give greater flexibility and refinement. Equally I often add helper columns into the data. at least in development stage so I can include/exclude certain data.

A pure Pivot solution?
Create a table showing all possible values for the Column Titles in the first column and the value you want in the results table for each. For items to exclude use "Other".

Add a helper column into your source data
Populate this with the values from the lookup table you just created using vlookup(...).

Use the helper column in your Pivottable. Make sure that this is always the first column in the data area (right click the field heading, Order, Move to Beginning)

Insert columns to the left of the pivot table and use simple formulae (e.g. =e3) to pull the values you want to chart into them.


Pivot rather then Functions
The potential VBA solution I alluded to in my earlier post involves:
Create pivotable
Hide columns (if these are variable then loop through a range containing the columns to show or hide)
Select the Pivot table area
Refine to select the visible cells only
Copy and PasteSpecial Values
Possibly fill the blank cells in the Rowfield area with the value from above (thread707-1629156)

You now have a table that includes correctly calculated percentages.

Using Functions in conjunction with VBA
Equally you could manually or with VBA create the table you need. (e.g. If the rows vary then programatically use advanced filter or similar to populate the row titles).

Then use formulae/functions that reference the row and column headings to get the data. These formulae could be GetPivotData or SumIF/CountIF/Sumproduct etc.

Possibly copy the data area to values to save memory etc.

Hope this helps.

Gavin

 

[feipezi]

Hi,

Thanks for your time and efforts. I will certainly try and see which approach fits more my situation.

Thanks.