Pivot table that pulls data from an online worksheet

[danjackson5]

Hi,

I'm sure this has something to do with External connections, but when i go to use it, i can only see references to access an OLE databases, not another spreadsheet.

I'm trying to set up a Pivot table on a local, empty spreadsheet but i need it to read data from a spreadsheet which is on our companies sharepoint site.

Googling has been very unhelpful as has my third party IT support provider - does anyone have a link that'll show me how i connect a local spreadsheet to an online one?

Very much appreciated - Thanks

Dan

[CK76]

SharePoint documents, if I recall, requires that you have specific license SKU for your Office. I believe it was ProPlus, but things may have changed.

When you go to data tab of Excel ribbon, and click on Get Data -> From File. Do you see "From SharePoint Folder"?

If you don't you may not be able to directly query it from SharePoint Online. You could set up synched drive on your local drive and query it from there.

[danjackson5]

SharePoint documents, if I recall, requires that you have specific license SKU for your Office. I believe it was ProPlus, but things may have changed.

When you go to data tab of Excel ribbon, and click on Get Data -> From File. Do you see "From SharePoint Folder"?

If you don't you may not be able to directly query it from SharePoint Online. You could set up synched drive on your local drive and query it from there.

Hi CK,

That's helpful. You are right, on my standard license, only a short list (Which excludes sharepoint) appears.

Online Services Greyed Out.JPG

However, both directors have the pro plus license and they can see more options

From Sharepoint Folder on Pro Plus.JPG

I went on the Sharepoint folder, put my site URL in and successfully authenticated. I see this screen but not sure what i'm doing from this point...

From Sharepoint Folder List of Files.JPG

Its good that i can see excel communicating with Sharepoint but it only seems to pull up a list of files. I just need to to read a particular file so i can pull the data into a PT.

Thanks

If the boss has to splash out to upgrade the licenses for our advisors then so be it...

[CK76]

No, it looks like they've extended availability of SharePoint connector. You don't need ProPlus.

Now, in the dialog window, you should click on "Transform Data". Once there, click on "Binary" of the file you wish to query.

It will show you list of Sheets and Tables in the file. Filter it to just the one you want and expand the Data column.

Once preview shows the table you want. Go to "Close & Load" button and choose "Close & Load to...".

It will give you dialog, make sure to click on "Connection Only" and also tick "Load to Data Model".

Now you can just insert Pivot Table using workbook's data model.

[danjackson5]

No, it looks like they've extended availability of SharePoint connector. You don't need ProPlus.

Now, in the dialog window, you should click on "Transform Data". Once there, click on "Binary" of the file you wish to query.

It will show you list of Sheets and Tables in the file. Filter it to just the one you want and expand the Data column.

Once preview shows the table you want. Go to "Close & Load" button and choose "Close & Load to...".

It will give you dialog, make sure to click on "Connection Only" and also tick "Load to Data Model".

Now you can just insert Pivot Table using workbook's data model.

This is great thanks. I definitely can't see any Sharepoint references in Get Data on my Office (Standard 365).

Capture.JPG

This wont be a problem if its only needed by the person creating the sheet i.e. me. I'm hoping people who don't have the Sharepoint Link can still refresh the data whenever they open the worksheet...

I'll have a play, thank you
+rep

[CK76]

Oh, I had missed that in your post where you mentioned only "Directors" had ProPlus.

[danjackson5]

No Probs. Question was answered perfectly. Many Thanks