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”
Ken,
I would think all the non-threadsafe functions (CELL being one, GETPIVOTDATA & INDIRECT are two others) would not be supported see http://msdn.microsoft.com/en-us/library/bb687899.aspx for a list.
Tom
Hi Tom,
Don't know about GETPIVOTDATA (will test later tonight), but INDIRECT does work in Excel WebApp. 🙂
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. 🙂
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.
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.
FEATURE COMMENTS
Legacy macro languages
Microsoft Excel 4.0 Macro Functions and Microsoft 5.0 dialog sheets.
Controls
Form toolbar controls, Toolbox controls, and all ActiveX controls.
XML
XML maps and embedded smart tags.
Security and privacy
Workbooks, worksheets, or ranges with protection, and workbooks that have Information Rights Management (IRM).
Ink
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.
Queries
Parameterized query tables.
External references to UDFs
External references (links) to user-defined functions (UDFs) in internal VBA projects, or UDFs in add-ins.
Comments
Display of and adjustment of comments.
Consolidation
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
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.
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.
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.