Indirect Formula - (Excel Online Version)

King Java 500 Reputation points
2023-02-07T19:44:34.17+00:00

Hello,

I'm trying to add an indirect excel formula in an online excel file. I need to link the indirect formula to other external online excel files. Basically, we have individual files by location that track performance.

I'm creating a new consolidated file that summarized the overall performance. I first did it on my desktop and it worked fine (all files were on my desktop).

Now that I uploaded the consolidated file to excel online, it's not working (all files are now in excel online). I tried making modifications but it's still not working. Does the indirect formula work in excel online? 

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,399 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Adrian Gallo 80 Reputation points
    2023-02-08T03:21:37.62+00:00

    Thank you for your question @King Java

    Yes, the INDIRECT function should work in Excel Online because it is part of Microsoft Excel's core functionality. I just tried it myself. When linking to external Excel files in Excel Online, however, the references in your INDIRECT formula may need to be modified. Instead of a local file path, you must use a URL pointing to the online file. A URL reference to an Excel file in OneDrive for Business or SharePoint should be formatted as follows:

    =INDIRECT("'
    

    You may also need to change your file-sharing settings to allow consolidated file access to the individual location files.

    I also considered using Office Script to accomplish this. Here's my attempt:

    async function run(context) {
      const range = context.workbook.getSelectedRange();
      range.values = [["=INDIRECT("'https://example.com/files/[file1.xlsx]Sheet1'!A1")"]];
      await context.sync();
    }
    

    Please keep in mind that this script assumes only one cell is selected and will insert the formula into that cell; additionally, the URL in my Office Script formula should be adjusted to match the location of the external Excel file.

    If you find my suggestion ineffective, please share your formula and steps so I can try to replicate it and help you resolve this.

    0 comments No comments

  2. Emi Zhang-MSFT 21,141 Reputation points Microsoft Vendor
    2023-02-08T08:07:18.33+00:00

    Hi @King Java,

    Based on your description, you want to create the external reference formula in Excel online. I suggest you refer to this support article and try the method to create the formula in Excel online directly:

    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

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments