• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Sharing dynamic Excel tables on Microsoft 365|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Sharing dynamic Excel tables on Microsoft 365|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Sharing dynamic Excel tables on Mic…
sp_PrintTopic sp_TopicIcon
Sharing dynamic Excel tables on Microsoft 365
Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
1
October 5, 2020 - 5:39 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 5, 2020 - 9:14 am
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
3
October 6, 2020 - 8:36 pm
sp_Permalink sp_Print

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

1.pngImage Enlarger

2.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 1.png (10 KB)
  • sp_PlupImage 2.png (11 KB)
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
October 7, 2020 - 6:36 am
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1549
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
October 7, 2020 - 9:02 am
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
6
October 7, 2020 - 9:12 pm
sp_Permalink sp_Print

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 

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
7
October 7, 2020 - 9:14 pm
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1549
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
October 7, 2020 - 10:39 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
9
October 8, 2020 - 5:04 am
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
10
October 8, 2020 - 9:30 pm
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
11
October 8, 2020 - 9:41 pm
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1549
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
12
October 9, 2020 - 10:08 am
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
13
October 11, 2020 - 6:05 pm
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1549
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
14
October 12, 2020 - 11:21 am
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 17
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
15
October 13, 2020 - 3:48 am
sp_Permalink sp_Print

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 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Nicholas Meyer
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Herbie Key
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Forum Stats:
Groups: 3
Forums: 24
Topics: 6526
Posts: 28576

 

Member Stats:
Guest Posters: 49
Members: 32809
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.