Excel non recognition/calculation of data in cells

M

mokwit

Something strange is happening when I cut and paste tables or financial statements from web pages.

The data or formula in the cell is showing up in the formula bar but is being ignored by excel formulas.

Example 1. 1 Cut and paste financial stements into ecel from web page. Then try and access this data from another sheet. I use the copy down function to copy the cells from the sheet I posted the web data to. What happens is the cell value of the first cell is copied into all the cells BUT the formula in the formula bar references the correct cell so if I copied down the column according to the formula bar the formula in the cells is changing correctly as I go down, e.g othersheet!A1 the one below OthersheetA2 but the value in the cell stays as the value of the firsdt cell entered manually. If I then say for othersheet!A14 delete Just the 4 using the formula bar and replace it with a manuall entered 4 via the formla bar it now gives the correct value for A14 in the original sheet (othersheet).

Example 2. I cut and paste a table into Excel and then try to perform a calculation in a cell referencing the table data – I get the error message #VALUE! . If I then type over the pasted value in the cell it calculates properly.

Is there some long forgotten option that I have forgotten to check or uncheck? Tried a fe like ‘ignore other applications’ and ‘transition formula evaluation’ both of which I have vague memories of causing me some kind of problem many years ago.

Any suggestions as to other forums to post this (e.g. specialised Excel forumds) welcome.
 
Press F9 and see if it updates your spreadsheet. If it does, you need turned off manual calculations and put it on automatic
 
Yep that was it for example 1. I was tacking something onto alarge model I have not used for a while.

Unfortunately example 2 seems not to be the same problem - its as if there was a function called "Paste numbers in form recognised as text" and I had it ticked. Numbers paste into cells and appear in the formula bar as a number but for calculation purposes they seem to be recognised as text - unless I type over one digit and then it calculates Ok rather than giving the #VALUE error message.
 
sometimes the queried data is not formatted as a number, but there is a hidden space somewhere. The usual obvious way to see this is when the numbers are justified to the left of the cell rather than all the way to the right. The way to undo that quickly is to copy and special paste the values, and select the operation Add button (bottom of special paste) in new empty cell set.

dt
 
Thanks to all for replies.

If I "paste Special" from the web page I get the selection choices of:
HTML,
Unicode Text
Text.

The text option pastes in a way that pastes to excel as all 1 column (pasting to a text file and then importing is an option, albeit a tedious one). When I paste as HTML, all numbers seem justified all the way to the right and there appears to be no double cell headings or blank columns.

After pasting as HTML if I copy and then 'paste special' values with add ticked in a fersh area of ssheet it only pastes 3 out of 9 columns (is there a clue in this?). I have tried various different pasting options and all produce #VALUE! in the calculation cells fed from the pasted data.

Another hoped for workaround of using the external data web query option fails as the web page is an *.asp file and is behind a password protected login page which is what I get if I paste the URL into IE.
 
Quote from mokwit:

Thanks to all for replies.

If I "paste Special" from the web page I get the selection choices of:
HTML,
Unicode Text
Text.

The text option pastes in a way that pastes to excel as all 1 column (pasting to a text file and then importing is an option, albeit a tedious one). When I paste as HTML, all numbers seem justified all the way to the right and there appears to be no double cell headings or blank columns.

After pasting as HTML if I copy and then 'paste special' values with add ticked in a fersh area of ssheet it only pastes 3 out of 9 columns (is there a clue in this?). I have tried various different pasting options and all produce #VALUE! in the calculation cells fed from the pasted data.
More...

For text or unicode import, you need to go to data/
text to columns in the file menu. Then
select what to use as a separating delimiter (, space, etc). It would be a lot easier if you just attached a very simple example of the problem .xls file you have imported in any of the modes above.
Use attach file below, and attach an example from your drive.

Also, what version of xl are you using?
2003 or 07?
 
Excel 2002 (!)

File herewith. The columns that compute are ones where I have typed over the data in the stock close column. Need to press F9 to calculate.
 

Attachments

you clearly have an extra empty char string on each side of the problem cells. I'll look a bit more for a solution in a bit.

Your automatic updates are also set to false, as confirmed by earlier poster.
 
Here is how to fix it without vba.
Remember I said left justify meant there are hidden character strings?
You will see it here.

The only other issue remaining, is that
some of the original cells have an extra digit (ex 17.91 vs 17.9). Try to import with equal digits if you can (i.e. include 4th zero); if not, you can find a way to work around with the above advice.

GL,

dt98
 

Attachments

You must log in or register to reply here.
Back
Top