Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Hi - does anyone know a piece of code that will change the range my pivot table reads from? Thanks -- LB79 ------------------------------------------------------------------------ LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156 View this thread: http://www.excelforum.com/showthread...hreadid=562856 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
You can use the offset function to accomplish this. There is a lot of good
help on this throughout the discussion group and on Debras site http://www.contextures.com (where I first learned about it) =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2) -The COUNTA(Sheet1!$A:$A) will adjust the number of rows for the table as new data is added. - You can change the 2 to the number of columns in your pivot table source data. For example if your data had 15 columns in it then change the 2 to 15. Optionally, if the number of columns will also change then change the 2 to this COUNTA(Sheet1!$1:$1). Hope this helps. "LB79" wrote: Hi - does anyone know a piece of code that will change the range my pivot table reads from? Thanks -- LB79 ------------------------------------------------------------------------ LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156 View this thread: http://www.excelforum.com/showthread...hreadid=562856 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Sorry, the full link to get to the correct page
is:http://www.contextures.com/xlNames01.html "MarkM" wrote: You can use the offset function to accomplish this. There is a lot of good help on this throughout the discussion group and on Debras site http://www.contextures.com (where I first learned about it) =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2) -The COUNTA(Sheet1!$A:$A) will adjust the number of rows for the table as new data is added. - You can change the 2 to the number of columns in your pivot table source data. For example if your data had 15 columns in it then change the 2 to 15. Optionally, if the number of columns will also change then change the 2 to this COUNTA(Sheet1!$1:$1). Hope this helps. "LB79" wrote: Hi - does anyone know a piece of code that will change the range my pivot table reads from? Thanks -- LB79 ------------------------------------------------------------------------ LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156 View this thread: http://www.excelforum.com/showthread...hreadid=562856 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Thanks for that formular. Do you know if there is a way to do it in VBA? -- LB79 ------------------------------------------------------------------------ LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156 View this thread: http://www.excelforum.com/showthread...hreadid=562856 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
I am sure there is, I don't know much about VBA. I have just started to
learn a little bit about VBA myself. You may want to try posting this in the programming group. "LB79" wrote: Thanks for that formular. Do you know if there is a way to do it in VBA? -- LB79 ------------------------------------------------------------------------ LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156 View this thread: http://www.excelforum.com/showthread...hreadid=562856 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Linking to a pivot table from a formatted invoice | Links and Linking in Excel | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions |