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.