concatenate

[Dave Breitenbach]

I'm using the concatenate function (or the "&") to combine 2 cells. One cell
is the number 2006 and the other cell is the number 1. I've added a space
using quotes in between so the answer should be "2006 1."
In one spreadsheet this works fine. I try the same procedure in another
open spreadsheet but it gives me the #value error. I've even cut the three
cells (the last of which includes the formula) to the new spreadsheet, and it
gives the same error. The links are correct, it just doesn't work
consistently.

Any thoughts?

tia,
Dave

 

[daddylonglegs]

So you're using a formula like

=A1&" "&B1

The only reason I can see for a #VALUE! error there is that A1 or B
contains a #VALUE! erro

 

[Harlan Grove]

I'm using the concatenate function (or the "&") to combine 2 cells. One
cell
is the number 2006 and the other cell is the number 1. I've added a space
using quotes in between so the answer should be "2006 1."
In one spreadsheet this works fine. I try the same procedure in another
open spreadsheet but it gives me the #value error. I've even cut the three
cells (the last of which includes the formula) to the new spreadsheet, and
it
gives the same error. The links are correct, it just doesn't work
consistently.

Tools > Options, select the Transition tab in the Options dialog, uncheck
Transition Formula Evaluation.

 

[Dave Breitenbach]

Thanks. That did it. I had always thought that the options you selected
applied to any workbook you were using in that version of excel until you
changed the options.
Can you explain what the transition formula evaluation is attempting to
accomplish?

 

[Harlan Grove]

Can you explain what the transition formula evaluation is attempting to
accomplish?

....

I could, but you'd get a more complete answer by pressing [F1] to bring up
online help, then search its index for the keyword transition.