| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert New Rows based on COUNTA() | New Users to Excel | |||
| Row Sequencing | Excel Worksheet Functions | |||
| formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
| finding the no. of rows in a COL filled with numbers, zeros and bl | Excel Worksheet Functions | |||
| Unable to Copy COUNTA() with cell references | Excel Worksheet Functions | |||
Hybrid Mode
