Hello!
Background:
Multiple Excel users have their own individual files.
Each user completes an Excel VBA userform to input new cases. Each case they add populates a new row in a table on a hidden worksheet.
A separate Power Query file resides in OneDrive or SharePoint. The PQ is fed by the tables on the hidden worksheet mentioned above, by looping through a folder and grabbing the information input by each user.
This all works very well.
Problem:
Two follow-up fields need to be completed in the PQ at a later date. This may or may not be added by the original user who input the case, which is causing the issue. If it was always the same user, I could just add another form that would add the extra two bits of information for a case and it would be fine. But because the case may reside in any of the users' files, that won't work.
I thought of adding a form to the main PQ in SharePoint/OneDrive, but if more than one user works in the PQ file at the same time, that won't work, as there will be conflicts. Additionally, it will require VBA code to do updates, which can't be done in SharePoint.
Possible Solution?
Each individual user will have an additional form in their file that the can use to populate the two additional follow-up case fields. But to do this, I will need:
A PQ based on the SharePoint/OneDrive PQ that includes the user ID, case numbers, and a few other fields to ensure they are working with the correct case.
This would also populate cascading validation lists for them to choose an ID and then case numbers related to that ID.
We could then either somehow point to each PQ in the local files and refresh the main PQ on SharePoint/OneDrive, or perhaps do a lookup of the row number in the main PQ and add the additional two fields of data. Or maybe merge two separate queries?
I'm very good in Excel and VBA, but not so good with Power Query, unfortunately. It's quite new to me.
I'm really stuck on the best approach. Any help would be GREATLY appreciated.
Thank you so much!
Why do the users have to have their own individual files, rather than writing to one central file (or database, or List)? It's making everything a lot more complicated.
Hello,
Thank you for your response.
How would they do that? Do you mean they go directly into the SharePoint/OneDrive PQ file?
If so, it would be because they don't want users to be able to view all of the other user's data.
Is there another way?
Thanks again!
Velouria,
You have me thinking...
If I understand correctly, the user should fill in the form that checks fields, creates a PDF, etc. then instead of writing to a hidden worksheet in their file, it should immediately populate the main PQ on OneDrive/SharePoint?
That makes perfect sense.
That said, how would I do that? Through VBA? I guess that's where I stumbled - not sure how to do that...
Thank you so much!
It would depend somewhat on the circumstances. I was thinking ADO, though I'm not sure how well that will work with Sharepoint as a back-end file location. It may be possible to use it to write directly to a sharepoint list if that's an option. Alternatively you might look into Power Apps or Microsoft Forms for the entry process, depending on what sort of volumes of data you are dealing with.
Who would be doing the additional entry, if it's not the original user?
Hi there,
They want the interface for each user to be Excel. This is done easily with VBA forms I designed. They click a button and the form populates. There are various questions that populate depending on which type of case they enter. This is all done with VBA and hidden sheets and works super well.
Populating the query on SP/OD works well, too. The issue is when a case needs the follow-up fields added. It could be anyone in the team who does that part, unfortunately. It would be very easy otherwise.
This is for distribution to various teams. Some might be using 365; some older versions of Excel. For this reason, I can't use anything that is only available in 365.
My original thought, and the application now as it stands, is to have the individual user's data populate a main power query. From the power query, they want managers to be able to view pivot tables and charts based on the PQ. Only the managers will have access to the main PQ file. Even at that, if one manager filters a pivot table when another manager tries to filter the same pivot table, for example there will be conflict issues. I will need to come up with a solutions for that as well, but it's not as crucial as the adding/updating cases issue.
Maybe I forget PQ and use VBA code to add a table row to the main file as soon as an individual adds a case.
I would need to access the main file on SP/OD through VBA. And worry about password protection. Also, I will somehow need to have a way to use the first couple of columns in the main file to serve as data validation lists from a user file to update the cases.
Is referencing a file in SP/OD, whether PQ or not, something that is easily done? I can easily do it when referencing files outside of SP/OD, but just don't have much experience or understanding of the difference when files reside on SP/OD.
Again, thank you very much for your assistance.
I know you can read/write data in Sharepoint lists using ADO, but I'm not sure if that will work to an Excel file data source on SP/OD. If I find some time, I might have a play with that later, as it could prove useful to me in some circumstances.
I'd suggest you look at Power BI for the reporting if you can. It will play better with multiple users.
Hello,
I don't know what ADO is, unfortunately.
Basically, I will want pivot tables and charts based on the main data. When the file is accessed by multiple users and one of them filters, for example, the filter will display for all users.
Will Power BI react differently in this case?
Thank you!
Yes, you can set up slicers and filters and what one user does will not affect what another user sees.
That's very interesting and very good to know. I will check on this.
So, it's different from Power Query in this way?
Power Query is part of Power BI, so you can do all the same data manipulation, before getting into the presentation aspects.
SharePoint Lists seem to be very interesting. I'm just learning about them now, so my apologies if my next question seems ignorant.
In my Excel file, I have multiple sheets. One for lists of questions depending on the type of case, one with instructions, and so on.
How do I integrate a SharePoint list that contains all of the data?
I'm also learning about Sheet View in SP/OD.
Maybe I can somehow set the default to be the view where sorting and filtering is only seen by the individual user, and doesn't affect other user's views?
I'm not aware of any way to force users into their own sheet views.
I don't really follow your question about Lists - can you elaborate? My thought was that you would only use the List to store the output from your workbook, not the inputs necessarily (though you could do that, then query the list from the workbook, which might make central maintenance of the questions easier.)
I have been working on this and am slowly getting there.
The issue that is causing the main problem is that User 1 may input a case initially, but User 3 may be the one that adds the resolution of the case.
Here is what I have come up with:
For Initial Case Input:
All users have a VBA userform that they populate when adding a new case. In turn, the data is added to a new row in a hidden worksheet that contains all cases for that user, for example, User 1.
The cases from ALL users populate a main Power Query in One Drive or SharePoint. This is done by creating the main query from a folder so it loops through all files in the folder and populates the Main Query from that data.
For Case Resolution Input:
A separate Userform is used to add the additional few fields that include the name of the user resolving the case, the date, comments.
The User selects a Client Name from a drop-down that is populated by a query that points to the Main Data query and then removed duplicates.
Once the Client Name is selected, a secondary drop-down filters only the cases for that client. This is done through a query that points to the Main Data query, which only displays the rows for that specific client.
Once the resolution fields are added, they populate a Resolution Query on OneDrive or SharePoint. This is a connection only and contains the client name, the case number and the additional few fields of data.
The common field for both the Main Query and the Resolution Query is the concatenation of the Client Name and Case Number fields, which serves as the unique identifier.
Adding the Resolution Query fields to the Main Query Fields:
I have tried creating Connection Only queries for both Main Query and Resolution Query, then merging them into a query that is loaded to a main Data sheet. From there, I can create pivot tables, charts, etc. based on the merged data.
However, it's very slow.
I watched a video by Mynda and Phil that touches on using indexes instead of merge to make it faster, but am afraid I'm a bit lost, as it uses product categories, whereas I would need an index for each row in the query and then look up the few fields from the Resolution Query to the Main Query.
I also created the Concat columns in each query directly in the query editor. I am not sure if this is faster, or if creating those columns in the tables in the individual user files would be faster.
I'm getting closer, but not quite there.
Any advice, or pointing to a video would be greatly appreciated, as always.