I copy an paste a web page into sheet 1.

Sheet 2, cell A10 has the formula: ="Sheet1!A"&(RIGHT(A9,2)+3)
Because the values I need to work with end up in every third row after
pasting(actually the web page results in Excel merging 3 cells together).

Sheet 2, cell F10 has the formula: =INDIRECT(A10) and results in 09
appearing as the result in the cell.

Now I want to do calculations based on that value of 9, but no matter what I
do I get a #VALUE! Error. I've tried setting the cell format of each of the
relevant cells to number, I've tried using the VALUE function to convert F10
to a number, which also gives a VALUE! error. I'm stuck.

Any ideas appreciated.
ed

Have you tried replacing the formula in F10 with

=VALUE(INDIRECT(A10))


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30473

The fact that you are seeing 09 rather than 9 suggests that you've got text,
rather than a number, in the relevant column. That is by no means unusual
when copying from the web. Go to the cell with the source data and look out
for spaces and other non-printing characters. Non-breaking spaces,
CHAR(160), are a frequent cause of such problems.
--
David Biddulph


"expect_ed" wrote in message
...
I copy an paste a web page into sheet 1.

Sheet 2, cell A10 has the formula: ="Sheet1!A"&(RIGHT(A9,2)+3)
Because the values I need to work with end up in every third row after
pasting(actually the web page results in Excel merging 3 cells together).

Sheet 2, cell F10 has the formula: =INDIRECT(A10) and results in 09
appearing as the result in the cell.

Now I want to do calculations based on that value of 9, but no matter what
I
do I get a #VALUE! Error. I've tried setting the cell format of each of
the
relevant cells to number, I've tried using the VALUE function to convert
F10
to a number, which also gives a VALUE! error. I'm stuck.

Any ideas appreciated.
ed

I can't replicate your error, but instead of

=F10
coerce it to a number with something like

=1*F10
or
=0+F10
or
=N(F10)

This shouldn't be necessary but it can't hurt.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 17 Nov 2008 06:35:01 -0800, expect_ed
wrote:

I copy an paste a web page into sheet 1.

Sheet 2, cell A10 has the formula: ="Sheet1!A"&(RIGHT(A9,2)+3)
Because the values I need to work with end up in every third row after
pasting(actually the web page results in Excel merging 3 cells together).

Sheet 2, cell F10 has the formula: =INDIRECT(A10) and results in 09
appearing as the result in the cell.

Now I want to do calculations based on that value of 9, but no matter what I
do I get a #VALUE! Error. I've tried setting the cell format of each of the
relevant cells to number, I've tried using the VALUE function to convert F10
to a number, which also gives a VALUE! error. I'm stuck.

Any ideas appreciated.
ed

shg - yes, =VALUE(INDIRECT(A10)) also gives the VALUE! error.

David - I think you are onto the problem, but how do I get rid of
non-breaking spaces formulaicly. I tried reading the web copy directly with:

=VALUE(TRIM(Sheet1!A61)) and it still gives me the VALUE! error.

Ifyou look at Excel Help for the TRIM function, you will see that it says
(inter alia):
"Important The TRIM function was designed to trim the 7-bit ASCII space
character (value 32) from text. In the Unicode character set, there is an
additional space character called the nonbreaking space character that has a
decimal value of 160. This character is commonly used in Web pages as the
HTML entity,  . By itself, the TRIM function does not remove this
nonbreaking space character. For an example of how to trim both space
characters from text, see Remove spaces and non-printing characters from
text."

I would suggest that you follow the latter link in Help.
--
David Biddulph


"expect_ed" wrote in message
...
shg - yes, =VALUE(INDIRECT(A10)) also gives the VALUE! error.

David - I think you are onto the problem, but how do I get rid of
non-breaking spaces formulaicly. I tried reading the web copy directly
with:

=VALUE(TRIM(Sheet1!A61)) and it still gives me the VALUE! error.

=substitute(a1, char(160), "")


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30473

That did it - Thanks

"shg" wrote:


=substitute(a1, char(160), "")


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30473