Log In
Register
Home
September 13, 2020
Offline
Hi,
Working in a team of three sectors, on MS 365 platform, Excel app, as a team leader I have one Excel file that consists of 4 sheets; master sheet, and 3 sheets divided by sectors from my team.
These 3 sheets are basically the same formatting tables, just like the master sheet.
Tables are simple data, they all have 4 columns, first is sector names (3 of them), second is client names, third are notes and fourth is simple column of 0/100, done/or not done.
I've made a Query that divides master sheet into sectors and those three sheets. So, when anything is changed in master sheet, after refreshing it reflects on other sheets respectively.
I need my 3 teams to access so that each team views their sheet without seeing other sheets. Only with view privileges. Also, all teams should be able to see changes from the master sheet whenever there are any. My team sectors are not power users.
I'm looking for a solution as simple as it can be, somewhere in MS 365 that could be dynamic and easily accessible to all teams, in way I've described. It even doesn’t necessarily have to be Excel file if there is a better solution, I'm opened for all ideas.
Thx in advance,
Nesha
See Answer
July 16, 2010
Offline
Hi Nesha,
Why don't you create 3 separate files that get the data from the master file? This would give each user their own view of the data and they can still refresh the query to get the latest info. Note: the master file must be closed for the query to be able to refresh.
Mynda
September 13, 2020
Offline
Hi Mynda,
Thank you for your reply. Well, I've done what you've suggested, I created 3 separate files and they are each connected by a query to the master file.
In order to share these 3 files to separate sectors within my team, and since we are all on Microsoft 365 platform, I had to upload all files on my OneDrive.
When creating a Share link I have another problem now, and that is that files won't refresh when opened in Excel web browser, but they will refresh when opened in Excel app.
See enclosed print screens of messages that I get when opening in web browser. And yes, master file is closed.
So, do you know, can you help me how to make a share link that those files will open in Excel app and not on web?
Nesha
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Without a file to check it is difficult to give any help. Some questions though that might help us to pinpoint where the issue lies.
What is the connection path in the PP query to the master file in the other three files? It needs to point to the file location in OneDrive.
Once you have opened any file from OneDrive in Excel app you can pin it, Have you tried that?
Br,
Anders
October 5, 2010
Offline
Hi Nesha,
If you are all running Microsoft 365 (presumably you are all part of the same business tenant?) then you should set up the OneDrive app on your PC's and sync files that way. You will then be able to access the files via Windows Explorer and open the files directly in Excel on your PC's.
Sync Files in OneDrive in Windows
Regards
Phil
September 13, 2020
Offline
Hi Phil,
Yes, my team including me already have installed and synced OneDrive, managed by IT.
I understand how to open in both cases, but members of my team are not power users, so when I share Excel files, they get generated link to their Outlook Inbox that when activated (clicked on), it opens web version.
My aim is to send them a link that opens in Excel app so they don't get stuck with messages I've sent previously. So, when I click on Share in Excel app, upper right corner, it automatically generates web link.
That is that I still don't know how to create (link)...and I don't see that what you propose gives that solution. Or am I wrong?
Thanks for your time and effort!
BR
Nesha
September 13, 2020
Offline
Hi Anders,
Also, thank you for your time and effort.
The path in Query is:
= Excel.Workbook(File.Contents("C:\Users\my name.my surname\OneDrive - my team name\my folder\my file.xlsx"), null, true)
It looks fine and it opens in (my) Excel app just fine. That should be for other users from my team (still didn't share to anyone).
So, I need a (generated) link that when used by users (from my team) will automatically open in Excel app.
That doesn't happen when you share from opened Excel file, upper right corner, Excel generates web link.
BR
Nesha
October 5, 2010
Offline
Hi Nesha,
What I was saying is that if you are all using OneDrive then you can access the shared file(s) directly from your PC either using Windows Explorer or from within Excel.
If your file is located at C:\Users\my name.my surname\OneDrive - my team name\my folder\ then browse to that folder and open the file. Your user's should be able to do this too. No need to generate sharing links and send them via email.
If you want to make it really easy for users, open the file in Excel then pin it to the list you see in the Open file screen. Or pin the Excel file to the Taskbar, or create a shortcut on the user's Desktop.
regards
Phil
Answers Post
VIP
Trusted Members
December 7, 2016
Offline
Hello Nesha,
As long the users that are to work with any of the three separate files have the master file in same path it will work just fine, if not then the query update will fail, as shown in 2.png in your previous post. Below is an example of a link (it is broken as I don’t intend to share) to a file in my OneDrive, you should have similar as the path to your master file If you intend to have it in OneDrive.
https://1drv.ms/u/s!AnqhyFgmH6.....ngc0yy5kep
You should be able to get good advice from the company IT department (I assume you have one) about how to go about. Another option is to use SharePoint or the old fashioned shared network folder.
Br,
Anders
September 13, 2020
Offline
Hi Anders,
All files including the master file are in the same folder on my OneDrive.
I will go with what Phil suggested, I will share link that is not auto generated but the one that I will copy/paste from Win/Explorer or Excel/Info-right click option and with little modification.
Thanks anyway!
BR
Nesha
September 13, 2020
Offline
Hi Phil,
So I cannot count on auto generated link since its made to be link to a web version, hence I just copy-pasted from Excel menu. It looks like either I just can't find a way or people at 365 didn't intended that way.
I had to do little modifications, my link is
https://aigo-my.sharepoint.com/personal/........../Documents/....../FILE.xlsx?web=1
But I had to get rid off "?web=1" part.
Also, I have to make email (to my team) myself and input this link. To get this link (for each file) instead of directly going from the Excel file to Share and chose people (since it would auto generate me the unwanted web link), in my web browser I have to not open the file, but click on OneDrive folder/File - Sharing and when on the right side Manage Access options opens, I have to choose Direct Access instead of Links giving access.
Hopefully at the end (in few days) will give the result I was looking for.
Thanks!
BR
Nesha
October 5, 2010
Offline
Hi Nesha,
I'm still not sure you are accessing the file(s) as I mean.
With the OneDrive app installed on your PC you will be able to access files on OneDrive from Windows Explorer.
So if you open Windows Explorer you can navigate to the OneDrive folder which you said is something like
C:\Users\my name.my surname\OneDrive - my team name\my folder\
On your team's PC's they will be able to do the same.
You do not need to use your browser and go into OneDrive online.
There's no need to create sharing links, the files are already shared/sync'ed (via the OneDrive app) and available directly on people's PC's.
You just need to make sure that the files are in a folder that is accessible to everyone that needs to use them. But as you are all using corporate OneDrive/365 this should just be a routine setup that your IT people can do.
Regards
Phil
September 13, 2020
Offline
Hi Phil,
I'm not sure does everyone in my team have OneDrive app, but they all have it as a web, and you did help me, well, you gave me an idea.
I intend to use your guidance, in a way... I will use my WinExplorer, create shortcuts for each file and use these links into my email for my team.
I'm pretty sure this will work, but yes, I do have to test it first, which I'm still not in position to do, hence mine "in few days".
BR
Nesha
October 5, 2010
Offline
Hi Nesha,
If you are all using MS 365 but not using OneDrive to sync files to the PC then you are not using the system to its full potential. It would make this situation a lot easier for you.
Cheers
Phil
September 13, 2020
Offline
Hi Phil,
But I am using MS365 and OneDrive. Files are there and all I did is opened OneDrive in Windows Explorer, created shortcuts (on my desktop) for each file, opened each properties, copied links and deleted "?web=1" from the end of each link.
And it works fine, users got emails with these links and it opened Excel app from each link.
Only thing is that I had to go this way, instead to use MS generated link, since it opens Excel web which won't do the job for my team.
Anyway, you triggered this idea in your post that I marked as useful answer.
Thanks again for your time, patience and effort, Appreciate it.
BR
Nesha
1 Guest(s)
