Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Pivot Table source
Hello all,
I haven't tried this yet, but I was wondering, when creating a pivot table based on data in the current workbook, if the source range can be dynamic using the OFFSET() and COUNT(A)() functions. Thanks for any help anyone can provide, Conan Kelly |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Pivot Table source
Hi,
Try this: go to Insert Name Define put the name you want in the name textbox (in the top) and put the formula below in the Referto textbox: =OFFSET($A$1,0,0,COUNTA(OFFSET($A$1,0,0,9999,1)),? ?) assumed the first cell of first column on the left of your data is A1 and you sould put the quantity of your data's columns instead "??" in the formula above click ok this name can grow up to 9999 rows so if you want more you should change the number 9999 to what ever you want. Thanks, -- Farhad Hodjat "Conan Kelly" wrote: Hello all, I haven't tried this yet, but I was wondering, when creating a pivot table based on data in the current workbook, if the source range can be dynamic using the OFFSET() and COUNT(A)() functions. Thanks for any help anyone can provide, Conan Kelly |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Pivot Table source
Farhad,
Thanks for the info. I read that somewhere before, but I keep forgetting that things can be done that way. Thanks again for all of your help, Conan "Farhad" wrote in message ... Hi, Try this: go to Insert Name Define put the name you want in the name textbox (in the top) and put the formula below in the Referto textbox: =OFFSET($A$1,0,0,COUNTA(OFFSET($A$1,0,0,9999,1)),? ?) assumed the first cell of first column on the left of your data is A1 and you sould put the quantity of your data's columns instead "??" in the formula above click ok this name can grow up to 9999 rows so if you want more you should change the number 9999 to what ever you want. Thanks, -- Farhad Hodjat "Conan Kelly" wrote: Hello all, I haven't tried this yet, but I was wondering, when creating a pivot table based on data in the current workbook, if the source range can be dynamic using the OFFSET() and COUNT(A)() functions. Thanks for any help anyone can provide, Conan Kelly |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Pivot Table source
Hi
There is no need to give a row restriction, and you can count the columns as well. Try =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) This assumes that your headers are in row 1, and that you do not have any entries in row 1 beyond your table itself. -- Regards Roger Govier "Farhad" wrote in message ... Hi, Try this: go to Insert Name Define put the name you want in the name textbox (in the top) and put the formula below in the Referto textbox: =OFFSET($A$1,0,0,COUNTA(OFFSET($A$1,0,0,9999,1)),? ?) assumed the first cell of first column on the left of your data is A1 and you sould put the quantity of your data's columns instead "??" in the formula above click ok this name can grow up to 9999 rows so if you want more you should change the number 9999 to what ever you want. Thanks, -- Farhad Hodjat "Conan Kelly" wrote: Hello all, I haven't tried this yet, but I was wondering, when creating a pivot table based on data in the current workbook, if the source range can be dynamic using the OFFSET() and COUNT(A)() functions. Thanks for any help anyone can provide, Conan Kelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Use Other Pivot Table as Data Source | Excel Discussion (Misc queries) | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
updating pivot tables using dynamic data source | Excel Discussion (Misc queries) | |||
Transforming pivot table back to the source table | Excel Worksheet Functions | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions |