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.

 

[ItIsHardToProgram]

=DECALER(TableauFinal![highlight]$A$1,0,0,NBVAL[/highlight](TableauFinal!$A:$A),27)

Sorry, I typo'd

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

 

[ItIsHardToProgram]

This is the VBA that assigns the #NAME?

With Worksheets("TableauFinal")
                        
                            Cells(iMaxCell, 6).Formula = "=+N" & iMaxCell & "-H" & iMaxCell & "-I" & iMaxCell
                            Cells(iMaxCell, 7).Formula = "=+N" & iMaxCell
                            Cells(iMaxCell, 8).Formula = "=+J" & iMaxCell & "+K" & iMaxCell
                            Cells(iMaxCell, 9).Formula = "=somme(O" & iMaxCell & ":V" & iMaxCell & ")"
                            Cells(iMaxCell, 14).Formula = "=+L" & iMaxCell & "+M" & iMaxCell
                            Cells(iMaxCell, 28).Formula = "=+W" & iMaxCell & "+X" & iMaxCell & "+Y" & iMaxCell
                            Cells(iMaxCell, 29).Formula = "=SI(AA" & iMaxCell & ">24," & Chr(34) & "BB" & Chr(34) & "," & Chr(34) & "Cell" & Chr(34) & ")"
                        End With

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

 

[SkipVought]

1. Rather than PASTE the formula. start with NOTHING in the RefersTo Textbox (just delete the text in the RefersTo box).
[tt]
This must be done with the source data sheet active.

In the Define Name window...

Enter =DECALER(

Use your mouse to select A1 in your source data

Enter ,0,0,NBVAL(

Use your mouse to select column A in your source data

Enter ),27)

ADD the formula.

Put your cursor back into the RefersTo box
[/tt]
What happens?

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

 

[ItIsHardToProgram]

The delimitation for the formula was not "," but ";" in my excel.



It now works.... !

Finaly, now one last issue, and I think we might be done!

I've lighten the filter macro and it now takes less than 10 seconds!!!

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

 

[SkipVought]

Each Cells object refer to the worksheet in the WITH statement, using the general coding technique...

With ParentObject
   .Object
End with

If you do not use the DOT Object, there is ambiguity that can cause errors.

With Worksheets("TableauFinal")

    .Cells(iMaxCell, 6).Formula = "=+N" & iMaxCell & "-H" & iMaxCell & "-I" & iMaxCell
    .Cells(iMaxCell, 7).Formula = "=+N" & iMaxCell
    .Cells(iMaxCell, 8).Formula = "=+J" & iMaxCell & "+K" & iMaxCell
    .Cells(iMaxCell, 9).Formula = "=somme(O" & iMaxCell & ":V" & iMaxCell & ")"
    .Cells(iMaxCell, 14).Formula = "=+L" & iMaxCell & "+M" & iMaxCell
    .Cells(iMaxCell, 28).Formula = "=+W" & iMaxCell & "+X" & iMaxCell & "+Y" & iMaxCell
    .Cells(iMaxCell, 29).Formula = "=SI(AA" & iMaxCell & ">24," & Chr(34) & "BB" & Chr(34) & "," & Chr(34) & "Cell" & Chr(34) & ")"
End With

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

 

[ItIsHardToProgram]

Adding the dot unfortunatly did not fix the problem...

There is still the #name? in both the somme and the SI cells...

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

 

[SkipVought]

Are you certain that there is some other syntax sublty in those formulas?

I am at a loss, because it works on my sheet, except I don't know what the SI function is.

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

 

[ItIsHardToProgram]

SI is IF

As I said, when I press f2 on the cell, and I press enter, the formula works fine, except for this green triangle on the top left, where it suggests to Widden the range of the sum, for it to cover more cells.

If you are lost, where do you think I stand?

I can work with the sum, just adding cell by cell, but this will leave the if problem, and I really liked the idea of having a dynamic table that adds and delete those rows and columns....

I will present this project tomorow, this won't necesserely affect the quality of the presentation, but it does affect the quality of the file itself... I am really loss....


=/

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

 

[SkipVought]

Does it solve anything to format all cells to the right of the source table as GENERAL, before you run the code in question?

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

 

[SkipVought]

Referring back to my recommendation to use MS Query rather than a PivotTable, the QueryTable Data Range Properties has a feature that "automatically" adjusts formulas in adjacent columns, to the returned rows in the QueryTable. It would render your current issue moot.

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

 

[ItIsHardToProgram]

Nope, I have formated ALL the cells in the sheet to general...

It still did not help


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

 

[SkipVought]

I still have no other suggestions.

With respect to "I've lighten the filter macro and it now takes less than 10 seconds!!!"

I have looked at the code in the workbook that you sent and have seen several obvious quick suggeations. Further analysis of your functional requirements and consequent code logic would take quite a while.

Quick hits:

Call ConvertToLetter(iColumn)
            
Worksheets(iSheet).Range(sColumn & "3:" & sColumn & iMaxCell).Copy

You do not need ConvertToLetter...

Worksheets(iSheet).Range(Cells(3, iColumn), Cells(iMaxCell, iColumn)).Copy

Avoid using the Select & Activate method...

Worksheets("TableauFinal").Cells(iOffset, iColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

Now that you have the PivotTable Named Range...

    [Database].Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Just a few.

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

 

[ItIsHardToProgram]

Well at least I am happy that further analysis would take quite a while

I know that theres alot of things I could have done better, I don't have the arrogance to say that I am an extremly well developped programmer, I am majoring in Finance and only used to program has a hobby, and now its become part of my job and I am learning.

So don't wory, I don't take any suggestion personaly, I am more than happy that people like you are willing to spend ALOT, and I like to emphasis on the ALOT, of their FREE time to help people who are actualy working to get something done...

I'll surely add the suggestions you give me tomorow, what was really making the structure heavy was the iterations I was doing for the pivot table, I removed most of these and Ill do a simple "how to do" for the exploiter.

Its too bad its not working for the formulas, I am mystified as of why its not working, strangely, if I do a search and replace in excel, (replace "(" by "(") the formulas are recognized correctly.

But when I do it by code, it still does not work... It might have something to do with the library of VBA????

Well, I somewhat am happy with the result so far, and I will gladly recommend some one like you, and remember what you did, in any aspect of my career.

I would give you more stars, because your job was in fact colossal, and you mearly ever got frustrated, nor asked for any kind of reward or renown.

Thanks alot, sincerely.

Julien ~

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

 

[ItIsHardToProgram]

FYI

by defining my source data with a ranged name, I was able to have 5 filter columns and 3 data columns.

Thanks alot 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 meant 6 filter columns

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

 

[SkipVought]

Things are looking up!

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

 

[ItIsHardToProgram]

Is there a quick way to remove all filters from a pivot table in a specific pivot column?

Faster than

For each pi
 pi.visible = true
next

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

 

[SkipVought]

I don't know of any other way.

BTW, VBA code questions ought to be posted in Forum707.

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

 

[ItIsHardToProgram]

Sorry my bad, I din't actualy realize I was in this thread.

Either way, thank you.

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