Adding to Pivot Table source data

K

K. Georgiadis

I have created a Pivot Table but, unfortunately, I did
not name my source-data range. Now I want to add more
rows to my source data but I cannot find a help topic
telling me how that is done. Can you help?
 
D

Debra Dalgleish

You can use a dynamic range as the source for the pivot table. There are
instructions here:

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

Name the range, then select a cell in the pivot table
On the PivotTable menu, choose PivotTable>Wizard
Click the Back button, and type the range name in the Range box
Click Finish
 
V

Vaughan

Right click anywhere on the pivot table, select the pivot table wizard and then go back to the dialog box for entering your range. Amend as needed and then click finish.
 
K

K. Georgiadis

Didn't work :-( I get a "reference not valid" error
I must have done something wrong when I defined the
range. My data is contained in worksheet "Data Base" in
the range A2:N57 -- 14 columns, with row 2 containing the
list headings.

I defined the formula as

=OFFSET(Data_Base!$A$2,0,0,COUNTA(Data_Base!$A:$A),14)

what have I done wrong?
 
D

Debra Dalgleish

If the sheet name is "Data Base", change the formula to:

=OFFSET("Data Base"!$A$2,0,0,COUNTA("Data Base"!$A:$A),14)
 
K

K. Georgiadis

Now Excel rejects the formula when I try to define the
range. I changed the worksheet name to DataBase thinking
that perhaps the space in the name may be causing a
problem, but that did not help. Do I perhaps need to
include the name of the workbook as well as the name of
the worksheet?
 
D

Dave Peterson

Try changing those double quotes to single quotes:

=OFFSET('Data Base'!$A$2,0,0,COUNTA('Data Base'!$A:$A),14)

But if you changed the name of the sheet to DataBase, then you don't even need
the single quotes (and xl will remove them).

After you get it working, you can check to see if you did what you wanted by:
Edit|goto
type the name you defined (not the formula, though).

Then hit enter.

You should see your range get selected.
 
Top