View Single Post
  #1   Report Post  
Old April 17th 15, 07:53 AM posted to microsoft.public.excel.worksheet.functions
[email protected] is offline
external usenet poster
First recorded activity by ExcelBanter: Jan 2015
Posts: 4
Default 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