Counta and rows..
 

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

Counta and rows..
 

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)

"Ju" wrote in message
...
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

Counta and rows..
 

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.

"Ju" wrote in message
...
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

Counta and rows..
 

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

--
Regards

Roger Govier



Bob Phillips wrote:
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)


"Ju" wrote in message
...
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

Counta and rows..
 

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

Roger Govier



Roger Govier wrote:
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


Bob Phillips wrote:
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)


"Ju" wrote in message
...
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

Counta and rows..
 

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" wrote in message
...
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

--
Regards

Roger Govier



Bob Phillips wrote:
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)


"Ju" wrote in message
...
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

Counta and rows..
 

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.

--
Regards

Roger Govier



Bob Phillips wrote:
Hi Roger,

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


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier



Bob Phillips wrote:
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)


"Ju" wrote in message
...
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

Counta and rows..
 

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

Bob

"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier



Bob Phillips wrote:
Hi Roger,

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


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier



Bob Phillips wrote:
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)


"Ju" wrote in message
...
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

Counta and rows..
 

Hi Bob,

Yes, it works perfectly. Thank you so much!

Ju

Bob Phillips wrote on 19-Feb-2006 9:17 PM:
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)

"Ju" wrote in message
...
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

Counta and rows..
 

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:
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.

"Ju" wrote in message
...
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