Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
IH IH is offline
external usenet poster
 
Posts: 2
Default Pivot Table Data Source external excel file

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Pivot Table Data Source external excel file

Try something like this:

From the Excel main menu:
<Data<Pivot Table
Use: Excel
To select your data...click the [browse] button
....select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"IH" wrote:

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Pivot Table Data Source external excel file

Q2: You can use programming to lock some of the pivot table features,
as described he

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

IH wrote:
Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?




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

  #4   Report Post  
Posted to microsoft.public.excel.misc
IH IH is offline
external usenet poster
 
Posts: 2
Default Pivot Table Data Source external excel file

Ron,

I'll try that again as I tried something similar. There is one issue. The
datasource isn't under my control ie I don't have write access to that excel
file. Let me see if I can just create a PT table from an external excel file
first.

Thanks for your help. I'll let you know soon.

"Ron Coderre" wrote:

Try something like this:

From the Excel main menu:
<Data<Pivot Table
Use: Excel
To select your data...click the [browse] button
...select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"IH" wrote:

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Pivot Table Data Source external excel file

This might work....

Build the Pivot Table on your PC using data sourced to a mapped drive on
your computer, then copy the file to the network.

On YOUR PC the pivot table source file might be
Y:\\DeptFolder\Analysis\datafile.xls
But....unless another user has their Y-drive mapped to that exact path,
their PC won't be able to find the source file when they try to refresh the
Pivot Table. Instead, the "Cannot open pivot table .... " message will
display .

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"IH" wrote:

Ron,

I'll try that again as I tried something similar. There is one issue. The
datasource isn't under my control ie I don't have write access to that excel
file. Let me see if I can just create a PT table from an external excel file
first.

Thanks for your help. I'll let you know soon.

"Ron Coderre" wrote:

Try something like this:

From the Excel main menu:
<Data<Pivot Table
Use: Excel
To select your data...click the [browse] button
...select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"IH" wrote:

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Create Excel Pivot Table Data Source [email protected] Excel Discussion (Misc queries) 0 July 12th 06 09:35 PM
Moving excel data into a pivot table Darlene Excel Discussion (Misc queries) 2 May 17th 06 11:24 PM
pivot table rmsterling Excel Discussion (Misc queries) 5 November 14th 05 05:40 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"