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