Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using Linked Workbooks with Different Users

0

I have created in a folder on a company's shared drive with workbooks that are linked (a 'destination' workbook and 'source' workbooks) using the excel copy/paste link method. 

The goal is to be able to have the manager use the 'destination' workbook (named Test Strategy) and link all of the other 'source' workbooks (software testers assigned to different testing project workbooks) back to the 'destination' workbook so the manager automatically gets updates on testing progress.

I am able to have both 'destination' and 'source' workbooks open and see the live updates to the 'destination' workbook.  

I am also able to just have a 'source' workbook open/make updates. When I open the 'destination' workbook, I am successfully prompted with the 'Update' message as expected.

So....when I have a 'source' workbook open and make changes and another user has the 'destination' workbook open, there is NO UPDATE.  Is it not possible for more than 1 user to utilize this linking method?

Kindest,

Porter

excel
MS Office Version: Office 365
posted by: Porter on Thu May 9, 2019 at 3:12 pm
Answer
Discuss

Discussion

Thank you for the quick response!  Unfortunately using a database is above my pay grade to get.  But to clarify, I am wanting to know if my mgr opens the 'destination' file (on shared drive) from her computer and a tester opens a 'source' file  (on same shared drive) from their computer and updates that 'source' file:  why does the 'destination' file not update? 

Thanks!
Porter May 13, '19 at 12:29 pm
I had a similar problem and suspect the Excel calculate has somthing to do with it.
You could try saving the source sheet as I think the links only look at the saved data and pressing calculate now in the formulars bar on the destination work book and see if that refreshes to your expectation.
I finally solved my problem by removing all the links and adding code to go and fetch the information. In my rollupStats sheet pressing the button set the code off to find all the TestScript sheets under the release folder (includes all sub folders) puts a line in for each project in the release showing total script  total scripted total passed total failed and total pending along with the tester names . It creates a time line for the release period showing number of tests passed each day. It builds  hyperlink to the test script so you can click to open should you wish to see the details. It provides grand totals and a RAG display based on a flat line from start to finish and total tests. As this is for the boss there is also a graph. The testscripts all start with a common identifier so there is no need for me to list them all as the code finds them. As more projects are added to the release these are picked up and combined in the next run.
k1w1sm (rep: 197) May 13, '19 at 4:52 pm
Thank you for your response.  I'm hoping someone can answer my question as to:
I am wanting to know if my mgr opens the 'destination' file (on shared drive) from her computer and a tester opens a 'source' file  (on same shared drive) from their computer and updates that 'source' file:  why does the 'destination' file not update? 

We are all on the same Microsoft 365 platform and have Excel's 'Calculation Options' set to Automatic.  The research I have done states that if the spreadsheets are in a shared drive and both open, they should update.  They do when I singularly have these spreadsheets open, but doesn't seem to update when 2 different people have 2 different spreadsheets open.  

Please help?
Porter May 14, '19 at 9:27 am
Did you try my suggestion of having the person on the source file saving their changes then have the person on the destination file saving the file and see if they appeasr. I think the problem is the the more than one instance of excel being involved.
I suspect that it appears to work on your machine as you possibly opened both workbooks in the same Excel Instance. You could test this theory by opening one workbook. then launch a new excel instance (Shift click on excel in the taskbar)  then see if the changes to source are reflected destination.
k1w1sm (rep: 197) May 14, '19 at 6:58 pm
I did try the suggestion of having the person on the source file saving their changes then have the person on the destination file saving the file.  Still doesn't update.  :(  

I am trying to understand why it works for one user in both files but not for 2 users - one in source spreadsheet and the other in destination spreadsheet.

Guessing it is not possible?
Porter May 17, '19 at 1:46 pm
Sorry Porter I missed that you are on 365.
I have only just got onto 365 and find that sharing has been removed from excel. I have had limited experience with direct linking of workbooks (none good) and  none in 365. I hope someone can help.
k1w1sm (rep: 197) May 19, '19 at 4:54 pm
Add to Discussion

Answers

0

Look into SHARED workbooks in excel.  They're good (but not as good as a Database)...there's a thing called Record Locking - when 2 or more people attempt to update the same cell on the same tab, Excel has a tough time with record locking.  This might work if the files are Shared. (perhaps a better solution may be a database?)

posted by: queue (rep: 467) on Fri May 10, 2019 at 8:45 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login