Child Item

Formula links in Excel

Hi!

I'm trying to organize different files in Dropbox with automatic links from one Excel-file to another. When I enter a formula to create this automatic link at first it seems fine, but when I close the file that the link refers to the formula automatically changes to =C:\Users\Username\...

Because the formula links to a place that only exists on my computer, my colleagues can't work with it. Is there a way to have the formula refer to a place inside of Dropbox instead of my computer?

Thanks for the help!

Find more posts tagged with

Microsoft Office

Edit

New Ideation Status

Comments

Mark

Hi @Stefan04

Simply put, no, sorry.

Thats because Excel will run each one locally and not on a shared server

Ian Yi

The problem is that Excel automatically converts the relative path of the linked file to an absolute path, which is specific to your computer and not accessible by others.
There are a few possible solutions to this problem:
One option is to use the HYPERLINK function in Excel, which allows you to create a clickable link to a file or website. You can use the share link feature in Dropbox to get the URL of the file you want to link to, and then use it as the first argument of the HYPERLINK function. For example, if you want to link to a file named report.xlsx stored in Dropbox, you can use this formula: =HYPERLINK("https://www.dropbox.com/s/abcd1234/report.xlsx?dl=0","Report"). This will create a link that says Report and opens the file in Dropbox when clicked.
Another option is to use the INDIRECT function in Excel, which allows you to reference a cell that contains the file path as a text string. You can store the relative path of the linked file in a cell, and then use the INDIRECT function to refer to it. For example, if you want to link to a file named report.xlsx stored in the same folder as the current file, you can enter the text =report.xlsx in cell A1, and then use this formula: =INDIRECT(A1). This will create a dynamic link that updates when the file location changes.
A third option is to use a named range in Excel, which allows you to assign a name to a cell or range of cells. You can create a named range for the linked file, and then use the name as a reference in your formula. For example, if you want to link to a file named report.xlsx stored in the same folder as the current file, you can select any cell in the file, go to the Formulas tab, click on Name Manager, and create a new name called Report with the formula =report.xlsx. Then, you can use this formula: =Report to link to the file.
I hope this helps you to create links between Excel files stored in Dropbox.

Stefan04

Hi Ian Yi,

Thanks for the possible solutions. I still have some problems setting it up though.

Let's say we have a document called "Overview" and I want it to read cell A1 from a document called "Results1" and in another cell read cell A2 from a document called "Results2". How would you go about that?

Your solution with the name manager looks like the easiest route to that, but I'm not succeeding in creating the right formula yet.

Stefan