Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 17th 15, 07:53 AM posted to microsoft.public.excel.worksheet.functions
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

Mystified

  #2   Report Post  
Old April 18th 15, 07:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2014
Posts: 27
Default 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.
  #3   Report Post  
Old April 19th 15, 10:41 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2015
Posts: 4
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CONCATENATE error? Martin @nohere.net Excel Discussion (Misc queries) 4 March 26th 10 07:10 PM
Concatenate Error NicoleS Excel Discussion (Misc queries) 1 November 17th 09 06:08 AM
Concatenate /w Chr(10) error Derek Bliss[_2_] Excel Programming 2 July 24th 08 07:47 PM
Concatenate error Susie Excel Discussion (Misc queries) 3 February 13th 08 12:56 AM
Concatenate error and Date Format Error Coal Miner Excel Programming 3 April 26th 06 10:30 PM


All times are GMT +1. The time now is 11:49 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017