View Single Post
Old 01-08-2013, 09:05 AM
TishyMouse TishyMouse is offline Windows XP Office 2007
Join Date: Feb 2012
Posts: 22
TishyMouse is on a distinguished road
Default Referencing a value over 255 characters within a table PLUS strange formula behaviour

Using Excel 2007

I have 2 tables side by side on a worksheet.

The first, Table1, is the 'master' table which I use to maintain data. THe intent of the Table2 is to provide a 'view' of the reference data table (with different column order, altered column headings and some calculated columns) to be used by other consumers of the data.

Most of the columns in Table2 refer directly to the value in Table1 on the same row e.g. using formulae similar to
=Table1[[#This Row],[Field_EXample]]

This works fine for most of the entries. The problem is that some cells, where the target value is over 255 characters the values appear in the target cell as a series of hash characters ############ (normally indicating a value too long to display). This is not helped by changing the magnification or the width of the column. THe format of the column is already set to wrap text. Is this a fundamental limitation fo EXcel? It doesn't happen if the formula is located outside a table. The 255 character limit sounds horrible familiar...

Also, probably unrelated, I then go to edit a value in Table2 (press F2 and enter) instead of the value being displayed I get the formula displayed in the cell instead. To get round this I need to select the column and do a search and replace of '=' with '='. Why is this? Note that 'Automatic' calculation is enabled.

Sample sheet is attached.
Attached Files
File Type: xlsx TableCharacterLimit.xlsx (16.2 KB, 7 views)
Reply With Quote