|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
Hi,
I haven't looked at your sample sheet but I am quite sure that both the behaviours you describe are because the table cells are formatted as Text. To remediate, select the entire problematic column, press CTRL+1 to open the Format cells dialog, choose General and click OK. That should fix it. |
#3
|
|||
|
|||
Hurrah, that works a treat. Thanks!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word VBA: Cannot Edit Range (Delete characters except the first in a table cell) | tinfanide | Word VBA | 3 | 04-27-2012 09:48 AM |
Referencing something that isn't numbered (eg table, figure etc) | hanvyj | Word | 2 | 04-10-2012 03:30 AM |
Odd behaviour of table - keep rows together | Stephan Lindner | Word Tables | 1 | 09-10-2011 05:31 AM |
Junk characters (box-like characters) in Word file | Sashikala | Word | 1 | 04-20-2010 02:03 PM |
Formula and Table Help! | steveman1234 | Word Tables | 2 | 03-28-2010 06:20 PM |