| Feedback | Checking spreadsheets | The CELL() function | |
You may wish to display the script of a formula contained in a particular cell. This may be for audit purposes, training other users or to assist with building a spreadsheet model.
The Excel ribbon option '[File] | Options | Advanced | (options for this worksheet) Show Formulas in cells instead of their calculated results' will affect a particular worksheet so that it displays all of the formulas rather than the normal calculated values. This is an all or nothing option. It can not be applied to the contents of particular cells.
- | M | N |
15 | Site Visitors | |
16 | ||
17 | Month | Number |
18 | May 07 | 9409 |
19 | June07 | 9160 |
20 | July 07 | 8564 |
21 | Total | 27133 |
22 | Average | 9044 |
23 | ||
24 | [N22] =$N$21/3 | |
25 | [M24] =FormText(N22,1) |
In the example above, the user defined 'FormText()' function is used in cells M24 and M25 to display the contents of N22 and M24.
Function FormText(CellRef As Range, Optional RefIndicator As Integer) As String |
The macro shown here should be entered into a Visual Basic module, either in
a specific workbook or in your (hidden) Personal.xls
macro workbook. It can then be used like any other Excel function - either typed
directly into a cell or selected from the |
A useful tool for checking cell contents is my 'SheetMap' spreadsheet. It creates a duplicate of a worksheet, labelling each cell according to the type of contents.
![]() |
Get this information as a document accompanied by Excel worksheets |
![]() |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
file:xlformtext.htm | Page last updated Jan 14 | © MeadInKent.co.uk 2016 | CMIDX S3 P9 Y |