Pivot Table Dynamic Data

[RoyinUK2000]

Does anyone know if it is possible to get a pivot table to automatically expand the range selection of data as it groes. I have a worksheet that grows daily and i am running a pivot table from this. If i refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.

Has anyone any idea's to help out a frustated guy.

Come on guys i need some assistance here!!!

Roy

 

[RoyinUK2000]

Please try and help with this one guys i am really really desperate to figure it out

 

[bolo]

Hi you can give your data a name that grows For example suppose we have the following in cols A and B:

Name Paid
bob 12000
tim 10000
roy 45000
gary 21220
tim 21312
gary 10000

Goto insert,name, define and in the formula bit write:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)
and call it data

When you make the pivot table instead of the range write this name.

You should find that when data is added, when you click refresh the extra information is updated.

HTH

 

[royUK]

Hi

Bolo is suggesting a Dynamic Named Range. For a good explanation and example workbook check out the Ozgrid website

http://www.microsoftexceltraining.com

Loads of info and help here if you haven't checked it out

 

[GeorgeB]

This works. Thanks to help from this board.

DYNAMIC NAMED RANGE

[1] Activate Insert / Name / Define,
[2] Enter EndRow as name in the Names in the Workbook box,
[3] Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,x!$A:$A)

Note. Replace x with the sheetname where the database is. ]

Example: If the name of the sheet is INPUT
=MATCH(9.99999999999999E+307,INPUT!$A:$A)

If you don’t have numbers (amounts) in column $A:$A then change that reference to a column that does.

Example:
=MATCH(9.99999999999999E+307,INPUT!$C:$C)

[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
(Just type over what is there)
[6] Enter as formula in the Refers to box:
(Again type over or alter what is there)

=OFFSET(INPUT!$A$1,0,0,EndRow,5)
(5 indicates the number of columns in the table. Alter to suit.)

[ Note. Replace 'INPUT' with the sheetname where your database is. ]

If your database does not start in row one change the formula.
For example if your database starts in row 10 then subtract 9 from the formula and change the first part to reflect where the top left corner of the database is.

Example
=OFFSET(INPUT!$A$10,0,0,EndRow-9,5) (Again change the sheet name.)

Remember the database starts in the row where the headings are.

[7] Activate OK.

Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area. When used with a Pivot Table it will update automatically upon Refresh Data.

NOTE: You must create the Pivot table AFTER you create the Dynamic Range.

 

[quickdraw]

Hi you can give your data a name that grows For example suppose we have the following in cols A and B:

Name Paid
bob 12000
tim 10000
roy 45000
gary 21220
tim 21312
gary 10000

Goto insert,name, define and in the formula bit write:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)
and call it data

When you make the pivot table instead of the range write this name.

You should find that when data is added, when you click refresh the extra information is updated.

HTH

I used this method with one variation. If I can't guarantee the field labels (column headers) will be followed by an empty cell, and since it may be early on in the development process I may not be sure how many columns will be in the data source for the pivot table (columns in sheet I want to include in the pivot table/chart) I identify a cell just past the area I'm working on, then use COLUMN to set my dynamic range relative to to that (mentally) defined point on the sheet.

Said another way, you can identify (mentally) a cell just past (to the right) the last column heading for your data area (the area the pivot table/chart will be created for) and use that cell as a reference point for the rightmost bounds of the dynamic named range you will base your pivot table/chart on.

Using the example above, you could say that A3 will always be outside the data area. Then you would do this:

=OFFSET(Sheet1!A1,0,0,COLUMN(Sheet1!A3)-1-COLUMN(Sheet1!A1))

Now I can add columns between columns 1 and 2 or between columns 2 and 3 and my pivot table/chart will pick them up on next refresh.

NOTE: I've had some problems with Excel not picking up added columns.