Excel WebApp – Formulas that don’t work

I was writing going to write up an article for my site tonight to show how to create a table of contents using native Excel functions, rather than resorting to VBA. Naturally, I figured that it would be great to put up an interactive example with the Excel WebApp, but I ran into an issue: I found a formula that works fine in the client, but just returns #VALUE! Errors in the Excel WebApp.

Debra did a great writeup of the =CELL function back in her 30 Excel Functions in 30 Days series. The examples work great in the client, but not the webapp. Too bad, really, as it's a great function that can be used for a lot of things.

I don't know how much people have played with this, but if you encounter a function that doesn't work, post it in the comments. It would be nice to get a full list.



7 thoughts on “Excel WebApp – Formulas that don’t work

  1. Yep, GETPIVOTDATA works as well. So it looks like it's not a threadsafe related thing. I've reported the CELL issue as a bug to Microsoft. Hopefully they'll fix it in future. 🙂

  2. Another thing that seems like it should fit in the "doesn't work" category: While the Hyperlink formula does appear to manifest correclty, it appears that no hyperlinks embedded in a WebApp file are clickable.

  3. Found the following in teh Excel Web App help:

    Features that prevent a workbook from being opened

    The following table describes features that prevent a workbook from being opened in the browser. To open a workbook that utilizes any of these features, open it in Excel.

    Legacy macro languages
    Microsoft Excel 4.0 Macro Functions and Microsoft 5.0 dialog sheets.

    Form toolbar controls, Toolbox controls, and all ActiveX controls.

    XML maps and embedded smart tags.

    Security and privacy
    Workbooks, worksheets, or ranges with protection, and workbooks that have Information Rights Management (IRM).

    All ink features including drawing, writing, and annotations.

    OLE and DDE
    Object Linking and Embedding (OLE) objects and Dynamic Data Exchange (DDE) links.

    Displayed formulas
    Workbooks saved with the formulas that are displayed.

    Data validation
    Preventing invalid data entry and creating drop-down lists.

    Data sources
    Data retrieval services for Microsoft Business Solutions, Windows SharePoint Services lists, Microsoft SQL Server, external data ranges (also called query tables), and tables linked to Windows SharePoint Services lists.

    Parameterized query tables.

    External references to UDFs
    External references (links) to user-defined functions (UDFs) in internal VBA projects, or UDFs in add-ins.

    Display of and adjustment of comments.

    Consolidated data from PivotTable reports.

    Digital signatures
    Visible and invisible digital signatures in a workbook.

    Attached toolbars
    Custom toolbars attached to the workbook by using Office Excel 2003 before the workbook was converted to Excel 2007.

    PivotTables that are based on the Scenario analysis tool. In addition, you cannot open PivotTables that are based on ADO recordsets, or PivotTables that contain server-based page fields.

    Scenario manager
    Workbooks that contain scenarios that were defined by using the Scenario Manager feature.

    Cell watches
    Workbooks that use the Watch Window to monitor and report on cell contents.

    Certain custom document properties
    Workbooks that contain document properties that sync to changes in a cell.

    Pivot tables with uncommitted writeback.
    Workbooks that contain PivotTables with uncommitted writeback operations.

  4. I figure it's internationalization. CELL's first argument is language-specific, so can't be generic across all language packs. Same for INFO. I believe these are the only worksheet functions which take specific string arguments.

  5. Maybe it's internationalization. CELL and INFO are the only functions which take specific string arguments, and those vary by language. I suspect INFO also won't work.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts