How do I extend the range of the data used in a PivotTable?
 

I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?

How do I extend the range of the data used in a PivotTable?
 

Hi
On the Pivot table, click on the pivot table wizard, slect back, m and
increase the range of the rows. It should just bne the number you need to
change. I would suggest, putting in a row range much bigger than needed, so
it will take into account all future extracts
Thanks
Matt

Jaybatz wrote:
I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

How do I extend the range of the data used in a PivotTable?
 

Hi
On the Pivot table, click on the pivot table wizard, select back, and
increase the range of the rows. It should just bne the number you need to
change. I would suggest, putting in a row range much bigger than needed, so
it will take into account all future extracts
Thanks
Matt

Jaybatz wrote:
I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

How do I extend the range of the data used in a PivotTable?
 

One Option -
On the Pivot Table definition declare the Range as a Name, for the example
I'm providing I call it BudgetData...

Save this little Macro to the Workbook to dynamically count the rows of the
Budget Data that are added during your weekly refresh of the data...

I made some assumptions here, that the raw data for your budget update would
exist in a worksheet "Budget Data" and that the budget data would start at
the top of the worksheet in cell A1...

Sub RowCount()
Dim RowCount As Long

Range("A1").Select
ActiveWorkbook.Names.Add Name:="DataTop", RefersToR1C1:="='Budget
Data'!R1C1"

RowCount = (Selection.CurrentRegion.Rows.Count)
ActiveWorkbook.Names.Add Name:="BudgetData", RefersToR1C1:= _
"='Budget Data'!R1C1:R" & RowCount & "C8"
End Sub

Run the macro each time you refresh the data... You may want to add a
button on an Assumptions page and assign the macro to it...

Hope this helps

--

Joe Mac


"Jaybatz" wrote:

I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?

How do I extend the range of the data used in a PivotTable?
 

You can base the pivot table on a dynamic range, as described he

http://www.contextures.com/xlPivot01.html

Jaybatz wrote:
I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com