Join same cells from different sheets as text

[Bob]

How do you bring together the same cell (text) from three
different sheets on a fourth sheet?

Example: Sheet1 Cell B2 is "red"; Sheet2 Cell B2
is "white"; Sheet3 Cell B2 is "blue"

Objective: On Sheet4 Cell B2 will read "red, white, blue"

Thank you.

 

[Bob]

Karen,

It does help; however, there are three unexpected results.

1. I type your formula in the desired cell, press enter,
and nothing happens. (Tools, Options, Calculation is set
for automatic.)

2. I press F9 and the desired text (red, white, blue)
appears in the the desired cell but the formula disappears
from the formula bar. (All subsequent attempts to view the
formula in the formula bar,and cell, are unsuccessful.)

3. I click, drag and drop the desired cell to the cell
below and receive the same text from above. (Consequently,
I can't drag and drop the cell formula and continue
getting a relative cell reference with a new result.
Example: In the cell below "green,yellow,purple" is
expected but red, white, blue appears again as though the
reference was absolute.

 

[Karen]

Dude, there's something messed up going on with your
Excel! Although, you really might want to double-check on
the fact that your calculation is truly set to automatic -
if it's on manual that would explain your numbers 1 and
3... (and then when you hit F9, the "green, yellow,
purple" bit should appear - it is NOT an absolute
reference, and it's not an equation that requires the RC
format to work when copied).

I have no idea why your formulae aren't appearing in the
formula bar. That's just not right, man...

- Karen