Unexpected #VALUE! error

[CM]

I'm using the following formula for a heading in numerous worksheets without any problems.

="12/31/" & VALUE(MID(A1,5,4))-1 & " OTHER TEXT..."

On one new worksheet, however, this formula generates a #VALUE! error. I copied it and the contents of A1 from another worksheet in the workbook where the formula is working correctly.

Any ideas why this isn't working correctly?

CM

 

[Frank Kabel]

Hi
what value do you have in cell A1?
Could you post your example data

--
Regards
Frank Kabel
Frankfurt, Germany

I'm using the following formula for a heading in numerous worksheets without any problems.

="12/31/" & VALUE(MID(A1,5,4))-1 & " OTHER TEXT..."

On one new worksheet, however, this formula generates a #VALUE!

error. I copied it and the contents of A1 from another worksheet in
the workbook where the formula is working correctly.

 

[RagDyeR]

A1 is probably Text.

First, check if there's an apostrophe (') visible as the first character in
the formula bar.

If not,
Try this:
Select A1, and
<Ctrl> <Shift> <~>,
Then <F2> <Enter>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I'm using the following formula for a heading in numerous worksheets without
any problems.

="12/31/" & VALUE(MID(A1,5,4))-1 & " OTHER TEXT..."

On one new worksheet, however, this formula generates a #VALUE! error. I
copied it and the contents of A1 from another worksheet in the workbook
where the formula is working correctly.

Any ideas why this isn't working correctly?

CM

 

[CM]

A1 "1/1/2004 Heading Label"
A2 ="12/31/"&VALUE(MID(A1,5,4))-1&" Subheading Label"

This formula works correctly in numerous other worksheets. I re-keyed it on one page and it evaluates correctly. If I re-key it on this page, it always evaluates to the error.

Thanks,

CM

 

[Peo Sjoblom]

You have Lotus transition turned on, tools>options>transition and uncheck
transition formula evaluation, most likely this particular sheet was once
created in Lotus 123

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

A1 "1/1/2004 Heading Label"
A2 ="12/31/"&VALUE(MID(A1,5,4))-1&" Subheading Label"

This formula works correctly in numerous other worksheets. I re-keyed it

on one page and it evaluates correctly. If I re-key it on this page, it
always evaluates to the error.