Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range in Pivot table
Hello I have a named ranged and in the refer to is this formula
=OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA( Pivot!$1:$1)) the problem is that it stops at rows 487 so everything ater row 487 is not included in the pivot table ? what's wrong with the formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range in Pivot table
Is there a value in every cell in the used range in column A?
If there are only 487 entries in that column, that's how many rows the dynamic range will have. Wanna Learn wrote: Hello I have a named ranged and in the refer to is this formula =OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA( Pivot!$1:$1)) the problem is that it stops at rows 487 so everything ater row 487 is not included in the pivot table ? what's wrong with the formula? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range in Pivot table
Are there any empty cells in the range which will "corrupt" the COUNTA count?
"Wanna Learn" wrote: Hello I have a named ranged and in the refer to is this formula =OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA( Pivot!$1:$1)) the problem is that it stops at rows 487 so everything ater row 487 is not included in the pivot table ? what's wrong with the formula? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range in Pivot table
Toppers
yes in column A there are some scattered empty cells "Toppers" wrote: Are there any empty cells in the range which will "corrupt" the COUNTA count? "Wanna Learn" wrote: Hello I have a named ranged and in the refer to is this formula =OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA( Pivot!$1:$1)) the problem is that it stops at rows 487 so everything ater row 487 is not included in the pivot table ? what's wrong with the formula? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range in Pivot table
..... that's your problem!
Try replacing the first COUNTA with: =MATCH(LOOKUP(10^10,$A:$A),$A:$A,0) This will ONLY work if data is numeric. "Wanna Learn" wrote: Toppers yes in column A there are some scattered empty cells "Toppers" wrote: Are there any empty cells in the range which will "corrupt" the COUNTA count? "Wanna Learn" wrote: Hello I have a named ranged and in the refer to is this formula =OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA( Pivot!$1:$1)) the problem is that it stops at rows 487 so everything ater row 487 is not included in the pivot table ? what's wrong with the formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dynamic range to create pivot table | Excel Discussion (Misc queries) | |||
Populate a table with a dynamic range | Excel Worksheet Functions | |||
Dynamic Range in a Pivot Table | Excel Discussion (Misc queries) | |||
Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Pivot Table - Dynamic Field | Excel Discussion (Misc queries) |