So, I'm trying to make an Excel sheet (we'll call this Problem Workbook) that is connected and pulls data from another Excel workbook in a SharePoint online folder. That sheet I'm referencing is a workbook spit out by Forms. I need to aggregate that Forms data into Problem Sheet for people to reference.
Here's my limitations:
- I have Excel 365; my colleagues do not. They're on 2016 or access Excel via Excel Online. Preferably, this Problem Workbook has best functionality with Excel Online.
- And thus, from my understanding, using PowerQuery isn't an option... But correct me if I'm wrong!
- This needs to function on dinosaur computers and not bog down computers that're 10 years old.
- The two people needing ALSO aggregate this data aren't nearly as savvy with Excel, which is why the end goal: I want things to be plug-and-play as much as possible so there's very little room to screw this up (lol).
What I want to do is have a workbook where the only thing my team has to do is enter the name of the Forms Excel workbook that its in a specific folder (and its associated sheet name), and it will then populate in the pretty-ified data into a new sheet into Problem Workbook.
I know that getting to that point is going to be a little complicated, but the first step is getting the dang variable URL to function, and it just won't.
So, for now, I've made it very visible to myself (the end result will have this all function in the background to not confuse my team). Data input of variable SharePoint URL (My company's URL and the SharePoint site is blurred.) In the photo, the yellow box is the Excel workbook I want to have variable.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | DATA | Company SharePoint URL | SharePoint site name | Shared Documents folder | folder | folder | folder | folder | EXCEL SHEET NAME | .xlsx | FORM NAME |
| 2 | INPUT | https://COMPANY.sharepoint.com/sites/ | SHAREPOINTSITE | Shared Documents | General | Rollout Results | 2023 | Rollout Excel Files | TEST ROLLOUT COMPLETION FORM | .xlsx | Form1 |
| 3 | OUTPUT | =" ' "&B2 | =C2&"/" | =D2&"/" | =E2&"/" | =F2&"/" | =G2&"/" | =H2&"/" | ="["&I2 | =J2&"]" | =K2&" ' " |
| 4 | |||||||||||
| 5 | =TRIM(B3&C3&D3&E3&F3&G3&H3&I3&J3&K3) |
So, I THOUGHT that if I used INDIRECT, it would work: Problem Workbook sheet with INDIRECT #REF! errors. But alas, when editing this on Excel Online, it just doesn't show the data and gives me the #REF! error. I double-checked the Connections, the connection is there to the Forms workbook. If I enter a connection the normal way, as in:
='https://COMPANY.sharepoint.com/sites/SHAREPOINTSITE/Shared Documents/General/Rollout Results/2023/Rollout Excel Files/[TEST ROLLOUT COMPLETION FORM.xlsx]Form1'!$A$2
It works just fine, but I don't want that lol. This is what that looks like, for reference: Excel Online example
When I open this in Excel 365 and check the Connections, it is there, and then, if I open the Forms workbook, the data appears. But this isn't functional for me. It seems to be that using INDIRECT breaks the Connection from my digging.
Does Power Query still function when using Excel Online, or do you NEED to use Excel 365 to make Queries query?
Can I set up Power Query in the sheet and let it work its magic for my team with backwards compatibility to Excel 2016 and Online?
Is there maybe an option I'm not thinking of to instead have something hosted in SharePoint Online? This doesn't have to be in Excel.
So, what are my options? How can I get it to where I can have someone enter the name of a workbook (and its associated sheet) and then have all of the formulas that are referencing that sheet update onto a sheet on Problem Workbook?
Probably super irrelevant hence tiny at the bottom but might be helpful: If you want a little bit of context as to what this is for, this data is Forms data for tests employees take at the end of modules my team sends out at various times a month. The Form captures their name, department, and some other irrelevant data (like email address), as well as their answers to the questions, which is why I need to make it pretty and sortable for the ~15 different managers who need to see if their employees took their tests.
