Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-08-2013, 09:05 AM
TishyMouse TishyMouse is offline Referencing a value over 255 characters within a table PLUS strange formula behaviour Windows XP Referencing a value over 255 characters within a table PLUS strange formula behaviour Office 2007
Novice
Referencing a value over 255 characters within a table PLUS strange formula behaviour
 
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, 1 views)
Reply With Quote
  #2  
Old 01-08-2013, 09:36 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Referencing a value over 255 characters within a table PLUS strange formula behaviour Windows 7 32bit Referencing a value over 255 characters within a table PLUS strange formula behaviour Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 01-08-2013, 09:39 AM
TishyMouse TishyMouse is offline Referencing a value over 255 characters within a table PLUS strange formula behaviour Windows XP Referencing a value over 255 characters within a table PLUS strange formula behaviour Office 2007
Novice
Referencing a value over 255 characters within a table PLUS strange formula behaviour
 
Join Date: Feb 2012
Posts: 22
TishyMouse is on a distinguished road
Thumbs up

Hurrah, that works a treat. Thanks!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing a value over 255 characters within a table PLUS strange formula behaviour 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 a value over 255 characters within a table PLUS strange formula behaviour Referencing something that isn't numbered (eg table, figure etc) hanvyj Word 2 04-10-2012 03:30 AM
Referencing a value over 255 characters within a table PLUS strange formula behaviour 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
Referencing a value over 255 characters within a table PLUS strange formula behaviour Formula and Table Help! steveman1234 Word Tables 2 03-28-2010 06:20 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft