I have a pivot table that currently uses a range in another tab in the
worksheet. I would like to change the source to an external source, but it
will not allow me to do so.

I am using the newest excel in compatibility mode. I have to do this because
the worksheet is shared with users that have an older version of excel.

Under Options/ Pivot Table Tools/Change data source, it gives you 2 options.
The first option (currently checked) is "Select Table or Range". The
second option, Use external data source, is grayed out and will not let me
select this option.

This worksheet is very extensive and contains alot of macros and cell links,
etc. that are based on this pivot table. I don't think I can just make
another pivot table using the external data that I need, I have to be able to
use the current pivot table and change the data source.

Please help me figure out why I cannot change the source for my pivot table!
Thanks!

Try this little utility:
http://www.contextures.com/xlPivotPlayPLUS01.html

File is named: 'PivotPlay.xla'

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"mcarter" wrote:

I have a pivot table that currently uses a range in another tab in the
worksheet. I would like to change the source to an external source, but it
will not allow me to do so.

I am using the newest excel in compatibility mode. I have to do this because
the worksheet is shared with users that have an older version of excel.

Under Options/ Pivot Table Tools/Change data source, it gives you 2 options.
The first option (currently checked) is "Select Table or Range". The
second option, Use external data source, is grayed out and will not let me
select this option.

This worksheet is very extensive and contains alot of macros and cell links,
etc. that are based on this pivot table. I don't think I can just make
another pivot table using the external data that I need, I have to be able to
use the current pivot table and change the data source.

Please help me figure out why I cannot change the source for my pivot table!
Thanks!