Forum Discussion

jkondrat14's avatar
jkondrat14
Copper Contributor
Nov 22, 2024

Pulling Data From ~300 Individual Sheets Onto a Master Sheet

Any all all help is appreciated!

I’m operating out of a company SharePoint, as multiple people need to be able to access these sheets. I want to have a Master sheet, that tracks information from ~300 individual site portfolios. I want the Master sheet to be able to automatically pull about 30+ data points off each portfolio and drop them into a single row, in a specific order. One row for one portfolio.

In my head, this works by me pasting a hyperlink (or something equivalent) in Column A, and all the needed data points automatically populating in the same row. This link serves two purposes. One, to provide a source for the Master to pull data points. Two, easy access to each site portfolio from the Master. Here’s a look at what I’ve tried so far:

VLOOKUP with HYPERLINK: This one I’ve had a little bit of success with, but I’m running into a logistical issue. I can get this to work in the sense that data points pulled get from the linked portfolio in Column A and get dropped into the same row. The problem is the data points only update when I “Open in Desktop”. If I edit a data point in a site portfolio while in Sharepoint, nothing gets updated on the Master. This won’t work for me, as most of the time my team will be editing portfolio sheets in Sharepoint.

Power Query: This one has promise, but I’m unfamiliar with PQ. Biggest hurdle I’m running into here is getting all wanted data points into a specific order in a single row. Each individual site portfolio has a ton of information, much more than what I want displayed on the Master sheet. With how much information in on each site portfolio, the data I want to pull is “all over the place”. Any advice on how to consolidate dispersed data into a single row using power query would be excellent.

Using VBA: This is by far the option that I have the least amount of experience in and would like to avoid if possible. I’ve never been skilled in any sort of coding or messing with macros. However, I’m always willing to try and learn, so if this is the best option, please let me know what I can try.

I appreciate the help from everyone. Please let me know if there are any questions or anything that I need to clarify. Thank you!

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Each individual site portfolio has a ton of information, much more than what I want displayed on the Master sheet. With how much information in on each site portfolio, the data I want to pull is “all over the place”. Any advice on how to consolidate dispersed data into a single row using power query would be excellent.

    That sounds to me that your site portfolios are not structured. So, the first step is to get a structure.

    Using VBA:

    VBA does not work in the browser.

    The problem is the data points only update when I “Open in Desktop”. If I edit a data point in a site portfolio while in Sharepoint, nothing gets updated on the Master.

    When you "open in desktop" you are still accessing the file from Sharepoint,

    The difference is the environment. One runs in Windows, one runs in a browser.

    From the MS support site:

    Note:  Excel for the web the HYPERLINK function is valid for web addresses (URLs) only.

     

Resources