concatenate within an excel formula

[sarah - northampton]

I am trying to concatenate cells and have the result execute within a
formula. I am ending up with: =sheet4!A2 instead of the value that is
contained in that location.

 

[Dave Peterson]

Format that cell with the new formula as General (or anything but text) and then
reenter the formula.

In fact, changing the cell's format to General, then hitting F2, then enter
should be enough.

If that doesn't help, maybe you're looking at formulas.

In xl2003 menus:
tools|options|view tab|uncheck formulas

ps.

You may want to use:
=if(sheet4!a2="","",sheet4!a2)

Then the formulas that point at empty cells won't show up as 0's.

 

[Dave Peterson]

Maybe I misread your question.

maybe you want to use =indirect().

Something like:
=indirect("'" & x88 & "'!" & x99)
where x88 contains the sheet name (sheet4)
and x99 contains the address of the cell (A2)

 

[Max]

It does sound like the cell where you input the formula was earlier
pre-formatted as TEXT (unknown to you of course), that's why. Re-format that
cell as general/number (via Format>Cells), then re-confirm the formula by
clicking inside the formula bar and pressing ENTER. You need to re-confirm
the formula to wake Excel up. Just re-formatting the cell alone will NOT
trigger it. Success? hit the YES below