Excel OFFSET function doesn't work in web browser with workbook references even though the external reference itself works

Question

Hello, the OFFSET function doesn't work properly in the web browser version of Excel (Office 365 build 16.0.15623.37952) when the first argument is a reference to a cell in another workbook. When I make a formula that has a simple, bare reference to another excel workbook/sheet/cell, the reference works properly, displaying the value in that cell in the online version. However, when I use that exact same reference as the first argument to the OFFSET function, like OFFSET(<ref>, 0, 2), it shows a #value error. It says "A value used in the formula is of the wrong data type". When I open the same workbook side-by-side in Excel Desktop and Excel in the browser, the correct result shows in the Desktop instance but the error shows in the browser instance. It is peculiar that the bare reference to the external workbook works, but not when used with OFFSET.

Answer 1

Hi Joseph

You probably can solve your problem if you use ";" instead of "," as argument separator in your formulas. At last this helped me. It's strange that the examples in the official documentation uses the wrong separator, though...

Well, after a little extra research, I found out that it actually depends on the locale settings whether formulas use commas or semicolons as argument separator. See here, or here. That's probably why your formula in Desktop Excel works, whereas it does not in Excel for the Web. Luckily, when you download the Excel file edited in the Web and open it in the Desktop Excel, the locale argument separator is used.

Gees, Excel is soooo messed up! I really wish there will be a future world without it.

Answer 2

Hi Waqas, thank you for helping with this.

I don't think this problem can be explained by the fact that no local file is opened when using Excel web, because references to another workbook work fine in Excel web, just not when used as the first argument to OFFSET.

It may be interesting to note that Excel web shows a different reference to the external workbook reference when viewing the workbook in a browser. The reference to the external workbook was created in the Excel desktop without any path information using a format like this:

'[<workbook_name>.xlsx]<sheet_name>'!$B2

However, in Excel web the reference is displayed with path information as a URL like this:

'https://<company_name>-my.sharepoint.com/personal/<username>/[<workbook_name>.xlsx]<sheet_name>'!$B2

Excel web is able to display values in external cells, and I think it may have something to do with the fact that it can "rewrite" the path to the external workbook as above.

I will try to provide more evidence of my example, but I would need to recreate the scenario without including any sensitive information. In the meantime, I think it would be informative to try reproducing the scenario. Start by creating an external reference to a cell in Excel desktop, alongside a call to OFFSET where the first argument is a reference to that same external cell. Then open the workbook in Excel web. The bare reference will work fine, but the formula that calls OFFSET will show an error.

Answer 3

Dear Joseph Leiba,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, please don’t worry, we can work together to narrow down and resolve the situation.

It seems that the Excel OFFSET function doesn't work in Excel for web and if you open the same workbook in Excel desktop client app it works.

One of possible reason why it doesn't work in Excel for web, because I think is OFFSET can only work when referenced file is open. For Excel web, I think it won't work, because no local file is opened.

As you mentioned, "like OFFSET(<ref>, 0, 2), it shows a #value error" please note that Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

Since you get the error message and f it is convenient for you, could you please share the affected workbook here (upload Excel workbook in OneDrive online location and share a link here) so that I can take a look from my side? we may need to check the elements in the formula with the data in your workbook.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad