Cells not recognised for charts

I have a table of public domain data from Nomis (The UK Gov't office
that provides data on employment).

I have created the table manually and it works fine. The Nomis one will
not display as a chart. I have copied the formatting of the manual one
to the Nomis version, so it's not that. All cells are 'general' in both
tables.

If I double click in each cell, it does something to it and it will
then be recognised but I cannot see anything which tells me what the
difference is before and after. 

If anyone can help me understand what the issue is, and even better,
how to solve it, I'd appreciate it.

I'm limited to Excel 97 SR2.

Please see example attached.

Many thanks,

Baldy


-- 
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1260
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 10:29:14 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
290 Views

Similar Articles

[PageSpeed] 19

Try a =Trim(A1) to remove trailing spaces.  These would be detected by
the =Len(A1) formula, and are removed in the trim function.

After the trim, you can Copy - and Paste Special = Values back over the
original data.


BaldySlaphead Wrote: 
> I have a table of public domain data from Nomis (The UK Gov't office
> that provides data on employment).
> 
> I have created the table manually and it works fine. The Nomis one will
> not display as a chart. I have copied the formatting of the manual one
> to the Nomis version, so it's not that. All cells are 'general' in both
> tables.
> 
> If I double click in each cell, it does something to it and it will
> then be recognised but I cannot see anything which tells me what the
> difference is before and after. 
> 
> If anyone can help me understand what the issue is, and even better,
> how to solve it, I'd appreciate it.
> 
> I'm limited to Excel 97 SR2.
> 
> I tried to attach an example, but for some reason it thinks the file is
> invalid (I can assure you it's not, and nor does it have macros/viruses
> or anything else funny about it.
> 
> Many thanks,
> 
> Baldy


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 10:36:44 AM
Bryan Hessey Wrote: 
> Try a =Trim(A1) to remove trailing spaces.  These would be detected by
> the =Len(A1) formula, and are removed in the trim function.
> 
> After the trim, you can Copy - and Paste Special = Values back over the
> original data.

Hi Brian,

I've tried a =Len(a1) and it does reveal that a sample field is
length=4, the visible field being '30.5'. However, I've done a trim and
tried paste special, values, to no avail. Checking the same field still
reveals a length of '4'. 

Argh! Any more ideas?

Cheers,

Baldy


-- 
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1260
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 11:08:08 AM
Addendum - when checking the original, non-functioning cells, there are
no trailing spaces appearing in the formula bar. In order to use the
new paste special values, as discussed above, I still need to click in
each cell so there is an active cursor. At that point, the numbers
align right (initially they appear align left) before they will pick up
for a chart.

Baldy


-- 
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1260
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 11:10:34 AM
Align left indicates text rather than numeric, try =Value(a1)



BaldySlaphead Wrote: 
> Addendum - when checking the original, non-functioning cells, there are
> no trailing spaces appearing in the formula bar. In order to use the
> new paste special values, as discussed above, I still need to click in
> each cell so there is an active cursor. At that point, the numbers
> align right (initially they appear align left) before they will pick up
> for a chart.
> 
> Baldy


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 11:50:00 AM
Bryan Hessey Wrote: 
> Align left indicates text rather than numeric, try =Value(a1)

That works, Bryan. But how come, even when I previously had changed the
format to numeric, it wouldn't work? 

Many thanks for your assistance!

Baldy


-- 
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1260
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 11:55:56 AM
Changing the cell format often has little effect on existing contents, a
MVP might explain that in more detail, to me it just doesn't apply
except to newly entered data.

Imported data often has problems, ours is with lists imported from a
phone system to be matched to one from Lotus Notes. 


Good to see that yours works.



BaldySlaphead Wrote: 
> That works, Bryan. But how come, even when I previously had changed the
> format to numeric, it wouldn't work? 
> 
> Many thanks for your assistance!
> 
> Baldy


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=489748

0
12/1/2005 12:02:55 PM
Changing the format doesn't change the value.  But it does prepare it for the
next change (and F2|Enter is enough).

I like this technique:
Select an empty cell
edit|copy
select the range of offending cells
edit|paste special|check add

Excel will coerce the text numbers to number numbers.



BaldySlaphead wrote:
> 
> Bryan Hessey Wrote:
> > Align left indicates text rather than numeric, try =Value(a1)
> 
> That works, Bryan. But how come, even when I previously had changed the
> format to numeric, it wouldn't work?
> 
> Many thanks for your assistance!
> 
> Baldy
> 
> --
> BaldySlaphead
> ------------------------------------------------------------------------
> BaldySlaphead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1260
> View this thread: http://www.excelforum.com/showthread.php?threadid=489748

-- 

Dave Peterson
0
petersod (12005)
12/1/2005 3:29:44 PM
Reply:

Similar Artilces:

Summing the numbers in a cell that conyains text
Hi...i need to add the numbers in several cells but the cells als contain text....how can i sum these numbers with out deleting the tex firs -- klatla ----------------------------------------------------------------------- klatlap's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1290 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 Hi could you provide some examples of your data. e.g. - is the number in the middle, the beginning or end of this cell - fixed position of the numeric part - etc. -- Regards Frank Kabel Frankfurt, Germany &...

How can I type within a cell like it is a word document?
Hi, Is there a way to type text within a cell where I can choose the position of the text by clicking within the cell like you can within a word document? I'd like to be able to easily type in something like this: YOU: ME: No I can not do that! (happy) Yes you can! And be able to select the placement of my text WITHOUT having to hold down the cursor to push it forward. Thanks! I...

Hide series names for unused data from chart legend
Hi! Does anybody know a way to specify that a chart legend does not display the name for a data series? Basically, I have 5 series plotted to allow for up to 5 modes on my chart, and all the points use the same coloured lines to demonstrate that they are all the same thing (modes). The legend wants to display the names for all of them ("Mode 1", "Mode 2", etc...) but I would prefer it to have just one entry (i.e. I would change "Mode 1" to simply "Mode", to show that any green line is a mode, regardless of how many there are - could range from 0 t...

How to plot an overlapping Tubes chart?
I have a table with two columns. There is some dependency between the two. I am trying to plot a Tubes chart with data range series in : Rows. I am able to plot first Tubes graph based on first column of table. I am wondering how to plot the second tube graph which should overlap the previous graph so as to provide a comparative study. In article <324A0E08-5C01-4A9C-88AF-7CC6BD9355FB@microsoft.com>, "=? Utf-8?B?T3ZlcmxhcHBpbmcgVHViZXMgQ2hhcnQ=?=" <Overlapping Tubes Chart@discussions.microsoft.com> says... > I have a table with two columns. There is some dependenc...

Automatically filling a cell from a worksheet based on a selection
I have an invoice form in which I would like to automatically insert the price based on the item selection in another cell. My data is a small table, 2 columns one for item, one for price. On my invoice for, I am able to select my item from a drop down box, now I would like to automatically fill the price column based on the item selection. What formula or function do I need to use. Use VLOOKUP() -- Gary''s Student - gsnu201003 "Pbrent" wrote: > I have an invoice form in which I would like to automatically insert the > price based on the item...

modify series data by changing chart lines?
I would like to change series data in a spreadsheet that has been line charted by manipulating the chart lines or trend-lines. Is that possible in Excel? Thanks for any help. ...

Cursor alignment with data points on scatter plot chart is offset
I have noticed that the selection cursor, when hovering over a scatter plot in the Windows version of Excel (2003 and 2007 versions), will display the value of the data point the cursor is supposedly pointing at. This is a handy feature, however, there appears to be a bug that directly impacts accuracy of measurements taken from charts when using this feature. I wrote "supposedly," because the data values displayed when hovering the mouse over a data set in the chart are for a datapoint about 5-8 pixels to the right, not the one at the tip of the arrow! This is an offset eff...

how can I use bullets in an excel cell?
Hold down alt and type 0149 on the numpad It's not formatting like in Word just a bullet looking character -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "bill" <bill@discussions.microsoft.com> wrote in message news:F23FB76B-E004-46BF-BE76-9BF68E6E3762@microsoft.com... > ...

how do i add a z or third axis to my chart?
I am trying to plot percentage costs vs time and I would like to show sales vol on a third axis. XL doesn't do real 3D charts. See Andy Pope's simulation of 3d XY Scatter Chart http://andypope.info/charts/3drotate.htm and my notes on 3D Surface http://www.tushar-mehta.com/excel/charts/3d_surface/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <2621791E-44B8-42AA-8A13-16376AB4BA88@microsoft.com>, =?Utf- 8?B?RnJhbmsgTWFjS2Vuemll?= <Frank MacKenzie@discussions.microso...

Skipping cells while reorganizing data
Everyday I take five sample of a chimical product. I take the reading for temperature and conductivity on the controller and then use excel to calculate solid ratios. The 5 samples give 5 different ratios. I then make the average of these 5 ratios in a merged 5 cells. I end up with 5 cells contening the same conductivity value, 5 cells contening the same temperature value and 5 cells merge in one contening the average ratio. I want to reorganize the data so I have one conductivity value, one temperature value and the average ratio. Is there a formula I can use to select one cell, the...

How to set text property of a check box to value of a cell?
I am building an Excel template. It has one worksheet with data (called "data") and many worksheets with craphics, built from the "data" sheet. One sheet with radar-type chart has number of check boxes which control hiding/unhiding columns. When a cloumn is hided, it does not produce a line on radar chart or a bar on bar charts. In the legend area user can see names of all series (=columns), used to build charts. But check boxes have their texts hard coded. I need to code a subroutine, which will be executed when a sheet with radar chart will open. It should assign...

Sort cells with same text descending
hi, Need some help with a spreadsheet which has the following setup aaa 111 bbb 223 ccc 345 ddd 564 ddd 123 Now what i need to do is have the highest similar text cells descending. i.e ddd 123 ddd 564 aaa 111 bbb 223 ccc 345 I would like the individual rows to be shifted in this order also, not just column A and B. Any guidance in the right direction would help! Thanks, T-DHM =) -- T-DHM ------------------------------------------------------------------------ T-DHM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30136 Vi...

over 65500 cells?
Hallo everybody! Any idea if it�s possible to get more then 65500 cells out of excel or is it impossible? Sweden -- Sweden ------------------------------------------------------------------------ Sweden's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27322 View this thread: http://www.excelforum.com/showthread.php?threadid=468912 Excel has 16,777,216 cells (65,536 rows by 256 columns) per sheet. If you want more ROWS (on one sheet)... the simple answer is NO, not possible. Good Luck -- swatsp0p ------------------------------------------------------...

how to count characters in a cell
Is there a non-vba formula to count specific characters in a cell? (ie A1=BILL, B1 shows the result 2 for a formula counting L's in A1) Thank you =LEN(A1)-LEN(SUBSTITUTE(A1,"L","")) Or, if mixed case, Dave Peterson says =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"l","")) Rgds, Andy Neat. Thank you. Bill "Andy Brown" <andy.j.brown@ntlworld.com> wrote in message news:#uE1hIkeDHA.2236@TK2MSFTNGP12.phx.gbl... > =LEN(A1)-LEN(SUBSTITUTE(A1,"L","")) > > Or, if mixed case, Dave Peterson says > > =LEN(...

find and replace does not work in a linked cell
I have a column in a worksheet that contains links to other cells within the same workbook, i.e. formula is ='lease Aug 2005'!F20. I want to perform a find on Aug and replace it with Sept (to update the cells with September's information) but am having no luck. It keeps looping into an "Update Values" dialog box. The workbook was originally created in Excel 2000 and I am now using Excel 2003. Any suggestions would be most welcome. Hi Judith- You're definitely on the right track, but you're missing one key ingredient. Before you use Find & Replace,...

copy part of cell contents to another column
I have an Excel sheet in which one of the columns contains e-mail addresses. I'd like to add a new column that contains the domain portion of the e-mail address (so basically anything after the @ sign), and keep the original column in tact. Is this possible? If so how? Thanks!! If the addresses start in A2, put this in the new column: =MID(A2, SEARCH("@", A2)+1, 999) -- TedMi "Glenn" wrote: > I have an Excel sheet in which one of the columns contains e-mail addresses. > > I'd like to add a new column that contains the domain portion of the e-m...

Cell size
I am trying to paste HTML code into a cell. When I do the size of the cell increases so I can't see the rest of my table. Also the HTML doesn't show up but the web page itself Can you please tell m 1)How to keep the shape of the cel 2) How I can view only the HTML and not the webpag Thank you in advance Dick, don't know if I can help you with (1), but if you want to paste HTML code into a cell, select the cell, type an apostrophe ('), press Ctrl-V, then Enter. (Later on you can press F2 to edit the cell and delete the apostrophe if you want). DDM "DDM's Microsoft ...

date cell format
i am working with an external data set (european) in which the date cell is formatted as this: dd.mm.yy when i sort by date, excel does not recognize the format and thus the initial sort is by day instead of by month. is there a way to fix this or get around it so i can sort by date and the result will be sequential? thanks! acb I'm not sure what's happening for you...but I have 10 cells with custom format of dd.mm.yy When I sort them, they do sor sequentially...I'm using Windows 2000 and Office XP. Wish I could be more of a help. Good luck, Dave M -- Message posted from http...

populate month-yyyy when compare 2 cells
Dear Forum, I have a scenario where I need to compare 2 date cells. When cell1 empty then take cell2 and format the date month-yyyy in cell3. When cell1 not empty then take cell1 date and format the date month-yyyy in cell3. i need advise how can i do that and create macro to automate the change. example c d e 20100220 feb-2010 c d e 20100521 20100220 may-2010 Hi Try =IF(COUNT(C1:D1), IF(C1,TEXT(DATE(LEFT(C1,4),MID(C1,5,2),1),"mmm-yyyy"), TEXT(DATE(LEFT(D1,4),MID(D1,5...

Cells that don't auto go back?
I will edit a cell by taking some info out and then I have a cell with 100 points of space and I only need 14 because I'm doing 14 point fonts. So I do a text wrap and the info just jumps up and down but the cell won't auto adjust. Is there a way to adjust the cell without doing it manualy. because if I have to do this to a dozen cells manualy it can be a pain. I hope I explained my problem otherwise just ask because this is really bugging me. Set the cells to wrap text and the rows to autofit. Gord Dibben MS Excel MVP On Sat, 12 Dec 2009 09:40:01 -0800, lostsoul62...

How do I do a vertical merge of cells in a column?
Select your range in the column format|cells|alignment tab check Merge Cells PAM wrote: -- Dave Peterson ...

Transfer cell data from one worksheet to another === URGENT
bello ALL, I do I transfer a cell data from one worksheet in Workbook A to another cell in another worksheet in Workbook B. Thanks, Raj Please stay in your thread You have an answer there already -- Kind regards, Niek Otten "rajesh" <rajesh@discussions.microsoft.com> wrote in message news:0DFD6622-7C55-4E29-9D2A-7E3F7B4BD2DC@microsoft.com... > bello ALL, > > I do I transfer a cell data from one worksheet in Workbook A to > another cell in another worksheet in Workbook B. > > Thanks, Raj ...

How do I unlock a cell in a page set up for pricing? changefactor
I have a cd from a vendor from which I price items. I was able to change the factor up unitl the other day. I change the factor so the pricing will reflect cost or retail. I do not know what the heck is wrong. Please, someone help me "Unable to Change Factors in Excel" wrote: > I have a cd from a vendor from which I price items. I was able to change the > factor up unitl the other day. I change the factor so the pricing will > reflect cost or retail. I do not know what the heck is wrong. Please, someone > help me Could you possibly give a bit more detail as to wh...

fairly easy (i thought) copy and paste cells, increment by 17 accross
Hi, I have a spreadsheet, that has on the first page data in J1,K1,L1 J2,K2,L2 J3,....... ...

Enter, tab, arrow keys aren't allowing me to move to other cells
I have a multi page worksheet with functions throughout. It has one sheet of inputs, other sheets hidden, input & answer sheets are protected. The Input sheet, several cells are available to input. Yesterday I could move around with the enter key, tab, and arrows. Today for some reason, the enter, tab, and arrow keys will not move me around. I made sure all input cells and function formatted linked cells are unlocked and unprotected. I have to click into another cell to move. ***I reboot, and the keys still don't work.*** I have been through all the options settings, can't find t...