Forum Discussion

Darren_Melbs's avatar
Darren_Melbs
Copper Contributor
Dec 12, 2022

Help needed! share data between worksheets

Hi all, first time user, so please go gentle 🙂 

 

I am using Microsoft 365 work account with the suite of products etc and I'm struggling to get two seperate Excel worksheets to share data, surely I'm missing just a simple step??.

 

I have found a support page, that clearly shows it can be done, however it will only copy the cell information and not turn that cell into an external link, so any information changed doesn't transfer across, which is my ultimate goal.

 

The Spreadsheet ideally will be left in SharePoint, however OneDrive is second best option, or any other options if neither available, I'm happy to take suggestions.

 

I was using my MacBook, however I also tried just the web (Safari) to complete and I previously tried on the work computer running the latest windows, all thus far have failed.

 

Any help would be hugely appreciated..

 

Cheers 

 

Darren

 

https://support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f#ID0EBH=Web

excel
Excel for web
Excel on Mac
Formulas and Functions
office 365

7 Replies

    • Darren_Melbs's avatar
      Darren_Melbs
      Copper Contributor
      Jan 16, 2023
      Yes. Thanks LZ. I’m not sure what happened to my post, however I did reply.
      Basically I said Thanks for your detailed explanation, it is between two seperate workbooks, I did try locally on my computer first, it simply adds the written link, so perhaps it’s a Mac issue?? It could be an issue with my Mac Excel, it is the enterprise version also. I know in the past I have managed to link on my old computer, so that is why I knew it does work.
      I’ve tried your solution, however for some reason it hasn’t worked.
      I appreciate your help thus far. Cheers.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 16, 2023

        Darren_Melbs 

        I'm not a Mac user... Hope mathetes will be able to help and you'll find a solution sooner or later

        Appreciate your reply - Thanks

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 29, 2022

    Hi Darren_Melbs 

    Ready to help as best as possible

    #1 One thing you should clarify: are you looking at linking info. between Worksheets inside the same Workbook (a Workbook can contain 1 to n Worksheets) or to link info. between 2 Workbooks?

    #2 The article you ref. is good, videos sometime help. After quickly watching a few, that one seems good to me. Couple of recommendations if you start from scratch in term of knowledge:

    - Do it with local file(s) first. By local files I mean files stored on your PC

    - Start with a something very simple: link cell A1 from one Workbook/Worksheet to another and stick to this basic scenario until you get it to work and can easily repeat the process n times

    - Don't try to write - by yourself - the external references (i.e. =[WorkbookName]SheetName!...). Only do it with the keyboard and the mouse

    #3 When you have it working with local files, the process is the same with files stored on OneDrive or Sharepoint. Assuming you want to link 2 Workbooks:

    a) Store the files (without any link between them) on OneDrive/SharePoint

    b) Open Excel

    c) Go to Open > Nativagate to your OneDrive/SharePoint folder (my OneDrive below)

    d) Select the Source Workbook

    e) Repeat c-d selecting the Destination worbook this time

    ... create the link(s) between your Source & Destination workbooks