CONCATENATE - #VALUE! error sometimes ????
 

I have a multi sheeted workbook, currently in EXCEL 2003.

On each sheet of the workbook, in cell A2 I have CZ in cell A3 I have 23 in cell A1 I have =CONCATENATE(A2,A3), which should give the result CZ23.

BUT ONLY SOMETIMES on some/most sheets I get #VALUE! error.

I have tried =(A2&A3) in both A1 and other cells but get the same working CZ23 or #VALUE! error

The sheets I thought are all the same, being copied from one original then the data changed.

Given that it works sometimes it should not be a global format error? though I have tried checking and unchecking TOOLSOPTIONSVIEWFORMULAS just to make sure.

I have tried formatting, Forrmat Cells, General, Number and Text all without success.

If I create a new sheet using a working version and copy in the data it works every time regardless if I copy just the data range (left drag select) or click the little square between A and 1 to select the entire sheet.

A new sheet works every time a new book works every time.

I have my solution, it'll take about half a morning to copy new sheets rename tabs etc, but would dearly love to know why some and only some of the originals return #VALUE! error?

PS Similar results in EXCEL 2007

Mystified

CONCATENATE - #VALUE! error sometimes ????
 

On Friday, April 17, 2015 at 1:53:55 AM UTC-5, wrote:
I have a multi sheeted workbook, currently in EXCEL 2003.

On each sheet of the workbook, in cell A2 I have CZ in cell A3 I have 23 in cell A1 I have =CONCATENATE(A2,A3), which should give the result CZ23.

BUT ONLY SOMETIMES on some/most sheets I get #VALUE! error.

I have tried =(A2&A3) in both A1 and other cells but get the same working CZ23 or #VALUE! error

The sheets I thought are all the same, being copied from one original then the data changed.

Given that it works sometimes it should not be a global format error? though I have tried checking and unchecking TOOLSOPTIONSVIEWFORMULAS just to make sure.

I have tried formatting, Forrmat Cells, General, Number and Text all without success.

If I create a new sheet using a working version and copy in the data it works every time regardless if I copy just the data range (left drag select) or click the little square between A and 1 to select the entire sheet.

A new sheet works every time a new book works every time.

I have my solution, it'll take about half a morning to copy new sheets rename tabs etc, but would dearly love to know why some and only some of the originals return #VALUE! error?

PS Similar results in EXCEL 2007

Mystified

Send file to me along with a copy of this post and I'll take a look.

CONCATENATE - #VALUE! error sometimes ????
 

On Friday, 17 April 2015 07:53:55 UTC+1, wrote:
I have a multi sheeted workbook, currently in EXCEL 2003.

On each sheet of the workbook, in cell A2 I have CZ in cell A3 I have 23 in cell A1 I have =CONCATENATE(A2,A3), which should give the result CZ23.

BUT ONLY SOMETIMES on some/most sheets I get #VALUE! error.

I have tried =(A2&A3) in both A1 and other cells but get the same working CZ23 or #VALUE! error

The sheets I thought are all the same, being copied from one original then the data changed.

Given that it works sometimes it should not be a global format error? though I have tried checking and unchecking TOOLSOPTIONSVIEWFORMULAS just to make sure.

I have tried formatting, Forrmat Cells, General, Number and Text all without success.

If I create a new sheet using a working version and copy in the data it works every time regardless if I copy just the data range (left drag select) or click the little square between A and 1 to select the entire sheet.

A new sheet works every time a new book works every time.

I have my solution, it'll take about half a morning to copy new sheets rename tabs etc, but would dearly love to know why some and only some of the originals return #VALUE! error?

PS Similar results in EXCEL 2007

Mystified

Unfortunately I have now deleted all the "defective sheets" and cannot seem to replicate the original problem. I'm still mystified and had wondered if somebody might be able to throw some light on the issue.

Cheers

Paul