Counta and rows..

[Ju]

Hi all,

I need help on the last part of the formula:


=OFFSET($A$1,(MATCH("Grand Total",A:A,0)-1),COUNTA("where the cell in
column a contains "Grand total"))

This is for referencing the grand total in a pivot table..

I can't use a static top row, as it may contain blank cells, as fields
are added or removed.


Thank you.

Ju

 

[Bob Phillips]

Hi Ju,

Perhaps?

=OFFSET($A$1,MATCH("Grand Total",A:A,0)-1,
COUNTA(INDIRECT(MATCH("Grand Total",A:A,0)&":"&MATCH("Grand
Total",A:A,0)))-1)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Barb Reinhardt]

I just created a quick pivot table with NAME and #. I went to a cell
outside the table and had it reference the grand total. This is what I got
for a formula

=GETPIVOTDATA("#",$A$3)

When I added more data items, I got this
=GETPIVOTDATA("Count of #",$A$3)
=GETPIVOTDATA("Sum of #",$A$3)

$A$3 is where the top right corner of the pivot table is located.

 

[Roger Govier]

Hi Bob

Doesn't that just produce the result "Grand Total"?
I'm not really sure what the OP is after, or what he wants to do with
the result, as the Grand Total row in the PT is going to provide the
various totals.
If he just wants to know which row the Grand Total appears on then
=MATCH("Grand Total",E:E,0) will provide the row number

 

[Roger Govier]

That should have read
=MATCH("Grand Total",A:A,0)
of course

 

[Bob Phillips]

Hi Roger,

No, it returns the last column in the row that contains Grand Total.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Bob

As you know, today is not a good day<bg>. The glow is increasing!!!
In copying yourr formula, and taking out the line wraps from the email,
I inadvertently removed the space between the last "Grand" and "Total",
hence the result it was returning for me.

Having corrected for that, the formula does return the value for the
last column in the Grand Total row as you say.
My apologies.

 

[Bob Phillips]

No problem, at least it is creating a bit more warmth over here <bg>

Bob

 

[Ju]

Hi Bob,

Yes, it works perfectly. Thank you so much!

Ju

Bob Phillips wrote on 19-Feb-2006 9:17 PM:

 

[Ju]

Hi,

I am not familiar with this function.
I tried, but it didnt work in my case because my fields get re-arranged
into many configurations very often.
But I will use it for other more fixed type of pivot tables.
Thanks.

Ju


Barb Reinhardt wrote on 19-Feb-2006 9:18 PM: