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]

Sorry, used the incorrect term.

The ROW COUNT in the COLUMN specified in the 4th argument, must have a value in EVERY ROW.

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

 

[SkipVought]

BTW, a good way to check that your Range is defined as expected is to click in the RefersTo: TextBox, AFTER you have ADDed the formula and observe the SELECTION (marching ants) surrounding your Named Range definition.

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

 

[ItIsHardToProgram]

Im not sure I understand, isnt this function:

=OFFSET(TableauFinal!$A$1,0,0,COUNTA(TableauFinal!$A:$A),COUNTA(TableauFinal!$A$1:$AA$1))

To count the number of rows to use, how is it then necessery to have values in every row???

Thanks alot, you've been colossal all the way, I hope people realize that.

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

 

[SkipVought]

[tt]
=OFFSET
(
TableauFinal!$A$1, anchor the reference here
0, no row offset from anchor
0, no column offset from anchor
COUNTA(TableauFinal!$A:$A), count the number of VALUES in column A
COUNTA(TableauFinal!$A$1:$AA$1) you might as well hard code 27
)
[/tt]
SO if there are EMPTY CELLS in the table in column A, you will NOT define ALL THE ROWS in the table, because COUNTA counts cells with VALUES.

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

 

[ItIsHardToProgram]

It does not seem to work, it seems that every time I try to paste my formula in the pivot table it says it does not recognize the source, yet my formula works in an excel cell... its as if the pt couldnt take the "offset"

It is has the following:

"=Offset(TableauFinal!$A$1,0,0,Counta(TableauFinal!$A:$A,27))"



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

 

[SkipVought]

[tt]
=Offset(TableauFinal!$A$1,0,0,Counta(TableauFinal!$A:$A[red])[/red],27)
[/tt]


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

 

[ItIsHardToProgram]

Also I have encountered a problem while populating fields with formulas through VBA.

The following:

Cells(iMaxCell, 9).FormulaR1C1 = "=somme(O" & iMaxCell & ":V" & iMaxCell & ")"

Returns '=somme(o'1':V'3')'

So I do this: (replace ' with nothing.)

Worksheets("TableauFinal").Range("F2:AG65536").Select
        Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

But then, the cell shows #NAME, and the only way for me to have it return the value is clicking on the cell and pressing enter, it then works.

Do you have any fix for this?

MSQuery is out of the question, and the time the macro takes is demoralizing me.

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

 

[ItIsHardToProgram]

Still no joy.

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

 

[SkipVought]

Cells(iMaxCell, 9).Formula = "=somme(O" & iMaxCell & ":V" & iMaxCell & ")"

your formula is NOT R1C1.

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

 

[ItIsHardToProgram]

Oh, what does R1C1 stand for???

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

 

[SkipVought]

"Still no joy"...

means exactly what?

ERROR?

Wrong Range?

WHAT?

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

 

[ItIsHardToProgram]

ok, sorry, I shall try to elaborate more in my answers, I am, as you I can assume, feel the project being alot heavyer than it should be....

Cells(iMaxCell, 9).Formula = "=somme(O" & iMaxCell & ":V" & iMaxCell & ")"

still produces a #name? in the cell, even though the cell is correct.

For the offset,

It still produces the same error "Can't read the source", basicaly it dosnt recognize the formula for some reason...

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

 

[SkipVought]

I assume that somme is the SUM function?

Please describe to me the step by step procedure you are going thru to enter the OFFSET function.

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

 

[SkipVought]

Can you enter
[tt]
=somme(O1:V1)
[/tt]
in a cell without error and get a correct value?

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

 

[ItIsHardToProgram]

Somme is the sum function,

There is also another cell wich has the same problem:

Cells(iMaxCell, 29).Formula = "=SI(AA" & iMaxCell & ">24," & Chr(34) & "BB" & Chr(34) & "," & Chr(34) & "Cell" & Chr(34) & ")"

The procedure I use step by step to change the data source range of the PT:

1-Right click on the PT.

2-Select Pivot table assistant.

3-Press back.

4-paste the formula in the cell where they ask where is the data (in french of course).

5-Press next.

6-Press end.



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

 

[SkipVought]

NO!

On the source data sheet...

Insert > Name > Define

Enter your range name in the Names in workbook text box

Enter your OFFSET function in the RefersTo: text box

ADD the named range

Go to your PivotTable

1-Right click on the PT.

2-Select Pivot table assistant.

3-Press back.

4-enter your source databse RangeName

5-Press next.

6-Press end.


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

 

[SkipVought]

still produces a #name? in the cell, even though the cell is correct.

What do you mean by that?

Can you, on the sheet enter =somme(o1:v1) sucessfully?

Can you, on the sheet, using Insert > Function... FIND and SELECT the somme function and enter that formula sucessfully?

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

 

[ItIsHardToProgram]

Can you, on the sheet enter =somme(o1:v1) sucessfully?"

Yes this is what I mean when I say the cell is correct.

The formula is correct, but it produces a #NAME?, as if VBA wasnt able to refer to the normal excel functions, thus not calculating them.


I can go through insert function also successfuly.

I can do a replace all ( by (, OUTSIDE of VBA, and every name is replaced by the correct answer...

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

 

[SkipVought]

1. Is the OFFSET function working for you now as expected?

2. When you EDIT (F2) the cell that is giving the #NAME! error and hit ENTER, do you still have the error?

3. Please post the VBA, again, that assigns the formula that is causing the #NAME! using COPY 'n' PASTE.

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

 

[ItIsHardToProgram]

1. No, it won't let me add the range, it says the formula contians an error and it highlights:

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

N.B. DECALER is the translation for offset and NBVAL for COUNTA

2. When you EDIT (F2) the cell that is giving the #NAME! error and hit ENTER, do you still have the error?

No, I do not have an error, that fixes it, but it "recommends" to add more cells to the range of sum. (Wich probably has nothing to do with this)

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