Formula works in one sheet but not another

[McAllan]

Greetings
I enter a formula to join text and number from 2 cells into one cell like ="Seven is "&7 in Sheet 1 and if I enter this in Sheet 2, it gives the #VALUE! error. It only seems to do it in this one particular file. I have tried the same formula in other files on the same laptop and on three other computers in the office and it works everywhere else. Excel 2007 is on all the machines. What am I doing wrong?
Thanks a million.
<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=146><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 4672" width=146><TBODY><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 110pt; HEIGHT: 35.1pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=46 width=146></TD></TR></TBODY></TABLE>

 

[Ruddles]

="Seven is "&7 isn't 'joining text and number from two cells'.


What's your actual formula?

(Does seem a bit weird though...)

 

[McAllan]

Hi Ruddles.
Thanks for replying so promptly. The formula is =Q26&" is "&N26 where Q26 is a name and N26 is a number. Again, the formula is simplified but still does not work.
The whole formula is a vlookup which looks in other workbooks for answers and I originally thought that this could be the problem. I made the formula more and more simple until I arrived at the one originally posted and I still get the error.
Thanks again.

 

[Ruddles]

Yes, that's the way I would have tackled it.

You haven't got any macros in the workbook, have you? Press Alt-F11 to get into VBA and Ctrl-R to make sure the Project Explorer is visible, then double-click the names of each worksheet in turn and finally ThisWorkbook.

Any VBA code visible?

Does it behave if you type a word or a number into it on its own? How about ="hello"&"world" or =123&456?

 

[McAllan]

No VBA at all. ="hello"&" "&"world" as well as ="hello"&"1234?" works just fine.

 

[Ruddles]

And =123&456? <!-- / message --><!-- sig -->

 

[McAllan]

="123"&"456" returns 123456 and =123&456 returns the #VALUE! error.

 

[Jonmo1]

="123"&"456" returns 123456 and =123&456 returns the #VALUE! error.

The clue that broke the camel's back.

Click Tools - Options - Transition
UnCheck "Transition formula evaluation"

 

[McAllan]

Hi jonmo1.
A man of few words I see. Awsome, Brilliant, Stupendous, (running out of adjectives here). Thanks again. I am afraid that I have fallen into the trap of “overlooking the obvious” by starting to wonder about the software when all the time it was just a good old dose of ignorance.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
To Ruddles, Thank you for responding so often today which kept me from going over the edge. I have NO doubt that your questions where designed to lead me to the answer which you obviously knew already and that jonmo1 just rained on your parade.<o></o>
To MrExcel Message Board, what a powerful forum and congrats on this.

 

[Ruddles]

To Ruddles, Thank you for responding so often today which kept me from going over the edge. I have NO doubt that your questions where designed to lead me to the answer which you obviously knew already and that jonmo1 just rained on your parade.

Err... no, actually - I was trying to find a pattern of behaviour and failing miserably!

I'm now going off to Google this phrase and add that bit of knowledge to my armoury. It took me several minutes to find the checkbox!