Pivot with source on different drive

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
Hey all..

I really hope someone can help me...

I'm trying to create a pivot table in a blank workbook.

The table should get it's source data from an excel-file on a different (network) drive.

I've tried something like this:

'p:\cars & bikes\[overview 2006.xls]vehicles'!$a$1:$m$2500

With 'p:\cars & bikes\' being the path to the file, [overview 2006.xls] being the file with the source data, 'vehicles' being the sheet-name with the data and a1:M2500 being the range....

Somehow this matrix doesn't work.

The pivot should be in a file with only the PT, taking data from the source upon 'update'. The PT should be on the users c-drive..

Anyone with proper knowledge?

Thanks in advance
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Joined
Jul 30, 2006
Messages
3,656
I have not tried this, but it should work.

In the blank workbook, click on:

Data, PivotTable and PivotChart Report..., External data source

Browse to the external data source.


Have a great day,
Stan
 

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
Thank you..

I tried that before, but excel prompts me for MS Query.. a feature not installed on my work station. Need IT dept. / admin to install it on the computer.. And make sure that it's installed on all the stations that needs this pivot table.

Was hoping there would be another way.

Even tried to record a macro that
-opened the source file
-copy the data from source to new wkbk sheet1
-close source file
-create pivot table on sheet2
-hides sheet1

This works the first time, but when trying to update, I get a message that all the fields already are in the table, and finally a debug-prompt ... :(
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
wrong way round...

open file with data & file you want the pivot in.

from the file with the data, set up pivot as desired. at the 'where do you want the pivot' stage, select existing worksheet & browse to the new file...
 

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
Thanks !!..

Seems like this is what I'm looking for.

The tests I tried on my own station worked like a charm. Just need to try copy the pivot-file to other stations to test.

Again, thanks :biggrin:
 

Forum statistics

Threads
1,137,059
Messages
5,679,369
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top