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

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" <(E-Mail Removed)> wrote in message
news:dt9o8g$qul$(E-Mail Removed)...
> 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
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:dt9o8g$qul$(E-Mail Removed)...
> 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
>
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:dt9o8g$qul$(E-Mail Removed)...
>> 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

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" <(E-Mail Removed)> wrote in message
>> news:dt9o8g$qul$(E-Mail Removed)...
>>> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:dt9o8g$qul$(E-Mail Removed)...
> >> 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
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:dt9o8g$qul$(E-Mail Removed)...
>>>> 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

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

Bob

"Roger Govier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >>> news:dt9o8g$qul$(E-Mail Removed)...
> >>>> 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
>
>

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" <(E-Mail Removed)> wrote in message
> news:dt9o8g$qul$(E-Mail Removed)...
>> 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
>>

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" <(E-Mail Removed)> wrote in message
> news:dt9o8g$qul$(E-Mail Removed)...
>> 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
>>
>>
>>
>>
>
>