Text vs. Formula

[Guest]

In a cell in worksheet “Metrics†I am trying to link to a cell in worksheet “Learning†– by clicking the equal sign in the formula bar, clicking the Learning worksheet tab, clicking the cell, and then clicking the checkmark in the formula bar. What appears is the formula, ='Learning'!D10, rather than the value in the source cell. Removing the single quote marks around the word Learning does nothing to change things

The target cell in the "Metrics" worksheet is formatted general, and in View>Window Options >Formulas, Formulas is unchecked.

Can someone help me straighten this out

 

[Tom Ogilvy]

This can happen if D10 in Learning is formatted as text. Select the cell
with the errant formula and another cell and do

Edit=>Replace
what: =
With: =

and it should re-evaluate the entry and treat it as a formula.

--
Regards,
Tom Ogilvy

In a cell in worksheet "Metrics" I am trying to link to a cell in

worksheet "Learning" - by clicking the equal sign in the formula bar,
clicking the Learning worksheet tab, clicking the cell, and then clicking
the checkmark in the formula bar. What appears is the formula,
='Learning'!D10, rather than the value in the source cell. Removing the
single quote marks around the word Learning does nothing to change things.

The target cell in the "Metrics" worksheet is formatted general, and in

View>Window Options >Formulas, Formulas is unchecked.

 

[Tom Ogilvy]

I named a sheet Learning and pasted in your formula on another sheet and it
worked fine.

I suspect that your sheet is not really named Learning. If may have a space
on the end or something. If you build the formula with the mouse, does it
show

='Learning'!X19

or does it show
='Learning '!X19

If the sheet were actually named learning with no spaces, Excel wouldn't put
in the single quotes (which you described in your initial post).

--
Regards,
Tom Ogilvy

Tom, Your solution fixed the problem and I will add the clarification to

my notes. Now another problem arises:

In a "Metrics" worksheet cell I enter the following:

=IF(Learning!X19<>"",Learning!X19,#N/A). The same cell parameters exist as
described for the D10 problem above. This time I get a #REF! However, if I
simply link the "Metrics" cell to "Learn" cell X19, the result is a
correctly displayed number. Why doesn't the formula work?

 

[Guest]

There was indeed a space behind the worksheet name - works fine! Thanks again for your help.