Unexpected #VALUE! error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi
what value do you have in cell A1?
Could you post your example data

--
Regards
Frank Kabel
Frankfurt, Germany

CM said:
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!
Click to expand...
error. I copied it and the contents of A1 from another worksheet in
the workbook where the formula is working correctly.
 
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
 
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
 
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)


CM said:
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
Click to expand...
on one page and it evaluates correctly. If I re-key it on this page, it
always evaluates to the error.
 
Back
Top