Excel sheets behaving differently.... 1

[wadjssd]

Hopefully there's a simple explaination for this quirky behaviour.....

I have a workbook with numerous sheets. In, say, Sheet1, a concatenation formula such as =A1&A2 gives the expected results, regardless of the contents of the cells A1 and A2.
However, on, say, Sheet2, the same formula, looking at cells A1 and A2 on Sheet2 which contain exactly the same data as the cells on Sheet1 behave strangely. If both cells contain text, the concatenation works fine. If I change one of the cells to contain a number, or a date, the result is a #VALUE! error.
Any ideas why this would work fine in one sheet and not another?

Cheers in advance,

wadjssd

 

[dsi]

Are the formats for the cells consistent between sheets?

 

[wadjssd]

Yep, checked all that. There seems to be no difference at all between the sheets, but still the mystery error continues....

 

[GlennUK]

You say that you are using a concatenation formula "such as =A1&A2", well ... is that the actual formula you are using on Sheet2? If not, what is the actual formula?

Have tried to recreate this error but without success. ( the #VALUE! error usually occurs when a mathematical function is passed a string argument )

Glenn.

 

[DaleWatson123321]

wadjssd,

How about use this formula...

=TEXT(A1,0)&TEXT(B1,0)

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

wadjssd,

In double-checking, I realized the formula should have referred to A1 & A2

=TEXT(A1,0)&TEXT(A2,0)

I'm curious to know if this (simple) solution was all that was required ?

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[wadjssd]

Hi - thanks for the replies,

Unfortunately, I'm still getting the same result. I have tested doing a concatenation in another location on the problem sheet, which works okay - but still no joy in the place where I need it!! Very weird. I have checked, double checked and triple checked all of the formatting etc., but it looks fine to me.
Aaaaargghhh!!

wadjssd

 

[DaleWatson123321]

wadjssd,

Sounds like a VERY interesting situation - one that "invites a challenge".

If you'd still like help in getting to the bottom of this, is it possible for you to "trim off" all the other "sensitive" parts of the file, and email the "troublesome" portion to me.

Seeing is believing, and it'll ALSO provide an opportunity to do some "first-hand" testing and experimenting.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[wadjssd]

Thanks to you all for your time on this, especially DaleWatson123321, whose "A3& Text(B3, Format)" method has provided a more than adequate workaround. A nice shiny star well-earned.
If anyone ever manages to solve this one 'properly', I'd be interested in the solution.

Cheers,

wadjssd

 

[dawnd3]

I never realized that you could use the & instead of the CONCATENATE function. That is pretty cool.

Dawn

http://www.calliopeconsulting.net