Pivot table - allocations 1

[ItIsHardToProgram]

I am currently having a problem with the way my Pivot Table manages my data.

What happens is the following:

I have different filters for my data, and I have 2 outputs that I need, both need to be shown verticaly, accordingly with my filters.

it should read like this:

A B C D SUM(Hours) SUM($)

Unfortunatly, I am unable to put both the fields in the "data" section, it says it does not have enough room to place the data...

but there is not more Hours than $, and I can make it work with either one, but not both... I hope this is clear enough, if not I will get back with a clearer explanation.

thank you all...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

So you have 4 row fields and 2 data fields, but it only works with one data field.

How many rows in your source data?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...also, what are the 4 data field names?

Do each data field need to be able to be filtered individually?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

My source data has around 1000 rows, but most of wich are duplicates, for a total of 389 filtered rows.

The 2 data fields are named: "Durée total" and "Total"

They need to be filtered individualy, meaning that for example:

A B C D Dur Total
1 1 2 3 #### #####
3 1 2 #### #####

and so on...


We need eventualy might need to manage more data, and so you can add a contingency of 500 data rows, 200 more rows in the pt...

Hope this helps you define my problem.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

That is a fairly SMALL source data table.

I see no reason that 4 ROW fields and 2 DATA fields will not work.

I just ran a test with over 1,000 rows in my source data, 4 ROW fields and 2 DATA fields and a ROW TOTAL.

Why do you have a Source Data field named TOTAL? Are you doing an aggregation on a total?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Well basicaly, the table is as I mentioned...

since they are duplicates, its called "Total" because its the total of the 3 months...

And its not its name, it was simply for the sake of the example...

I don't see why it wasnt working either, maybe I could send the excel spreadsheet to you?



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

You should find it shortly, thanks again for your help.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I will sent you a lighter version, I doubt 32 meg will pass through...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I missunderstood, that you have SIX ROW fields and ONE DATA field, and that prohibits you from adding a second DATA field.

To address this issue directly, I'd suggest doing one of these things: 1) make a new column in source data concatenating the Surname and Given name into one field, or combine the Mobile Nbr & BB ou Cell, threby reducint the number if ROW fields in the PT.

In addition, I recommend the following, with regard to your source data sheet.

1. Isolate the data in your table from data in other columns

2. Delete the unused formulas that exist in rows below the table

3. Define your source data in the PivotTable Source Data window, as only the rows with data; in your example TableauFinal!$A$1:$AG$968

If your source data table is dynamic, use a Dynamic Range Name to define the table range. faq68-1331 The Range name Database is a good one to use.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, so basicaly I can't have that many rows with alot of data in my PT...

Just for the sake of my understanding, for my example, it would be somnething like

=OFFSET
(
INDIRECT("Sheet1!$A$2"),
0,
0,
CountA(Sheet1!$A:$AA)-1,
1
)

if the columns stop at AA ? how does it work???

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I also recommend having one and only one table on a sheet and nothing else. If that were the case, argument 4 could be calculated as CountA(TableauFinal!$1:$1)

[tt]
=OFFSET(TableauFinal!$A$1,0,0,COUNTA(TableauFinal!$A:$A),COUNTA(TableauFinal!$A$1:$AA$1))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Well you see that might be hard.

What happens is that files are being downloaded, on a monthly bases, and each file need to be manoeuvered a bit, and then they are ready to work with.

What I do is that, the UNEXPERIMENTED USER (this is my main problem), has to be able to work with the file I created without any problem...

Basicaly, he inserts the sheet, names them correctly, run the macro, and voilà, he has his reports.

They will be reevaluated on a quaterly basis, so each quarter, the user comes, insert 3 sheets, and does the same thing, and so on....

Ultimatly, once the macro has been run, I can tell the user to delete the sheets, thats not really a problem, but is it really necessery?.

There will be around 10 reports, meaning 10 pivot table, the y will all use the same table, and will mostly be applying different filters, in order to be print ready, again for the unexpirmented user....

I've given up on a macro to print since this would mean changing the drivers we already have on the computer, the user will have to live with being able to print...

I was also thinking, do you have any idea on wich type of error I should be trying to catch here??? I will probably won't be there to manage the macro, so it has to last a while....

Thanks alot again for your help skip, I would give you two stars if I could...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I have tried what you suggested, I've even removed two rows, making the PT of only 4 rows and one data, and its still not adding the Durée Total in the data section....

Why is that???

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

1. Paste this procedure in a module and run.

Sub CleanMyPivots()
'Skip Metzger/2007 Apr 30/817-280-5438
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

2. Remove existing name and mobile fields and add combined fields.

3. Remove any SUBTOTALING from the ROW fields.

Doing these things, I was able to include another DATA field.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

It works, but there are two things that have me worried, one of wich has me really worried.


First issue, quite easy to fix, I am not able to format the data so that it is only shown in 1 rows, example:

It shows like this:

1 2 3 4 Data
x x x x sum ###
sum ###


The 2nd issue has me rethinking the whole thing...

Is there any chances that this file I created could be unstable?

It seems to me very fragile as of the necessity of clearing the cache, and the "heavyness" of my macro.

Do you think I should be considering something else?

Access is out of the question since there are no experimented users with access.

Thank you,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Also, I can't seem to apply the offset to my sheet, it says it does not recognize =Offset etc....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Please use TGML Tage (search in this page - READ - USE)

Don't know what your FIRST issue means.

SECOND issue: PivotTables do have their limitations, which is why a developer ought to have as many tools in his/her toolkit as possible. I would use MS Query to generate my report for the source data. You would have much more control over the output, formatting etc.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

First issue is fixed, I will in fact check out the availability of MS query with the IT's.


I still can't make the offset work for a dynamic range of data.

Ive tried both formula's, it does not even recognize the offset it seems. (I did translate it to french functions)

Any clues on why this is not working?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Your 4th argument, the COLUMN COUNT, must have a value in EVERY ROW. You cannot use this on a column that has empty values in that column.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}