SOLVED

HELP! Excel displaying link instead of linked cell value? (='Short List'!D31, instead of Jeff).

Occasional Contributor

I have a workbook and I have a list of people in the worksheet titled "Short List" once they are qualified, I move them to another sheet "Qualified".  

Yesterday the Qualified sheet stopped showing the values of the cells from "Short List" and will only show the link, (i.e. ='Short List'!D31).  

 

Not sure what is up, have never encountered this before.  HELP!!!

12 Replies

Hi Donald,

 

Please check in ribbon Formulas tab if Show Formulas is not active

Sergei Baklan
No, not active. When I toggle it, the formulas in the res of the cells show up. Toggle it again, and they go back to values, EXCEPT the ones I have investigated.
So, to be clear, I clicked on the cell that said "jeff", it then displayed ='Short List'!D31, can't get it to go back and display "Jeff" (which is the value in the linked cell).
I cleared the cell and tried it again, and still only shows ='Short List'!D31.
I checked several other cells and the minute I investigate the cell, it reverts to the formula.
So my sheet has some values (because I have been working on this for a week and it has been fine until last night), and two formulas.
I imagine any other cells I investigate will follow suit with the first two.

Donald, and you you sure these cells in question are not formatted as text? If, for example, you reformat cells from General to Text after the clicking they will show you the formula only

Absolutely. That was the first thing I checked.

Also, it was WORKING for a week and just stopped.

I am TOTALLY puzzled by this.

If you could submit the file removing all sensitive information and keeping only few cells (which work and ones in question) that will be easier to check.

Sergei Baklan

 

I removed the other sheets and changed some data to protect the identities 

best response confirmed by Donald McAlister (Occasional Contributor)
Solution

@Donald McAlister, the problem is if your source cell formatted as text the linked cell is automatically reformatted to text when you enter or edit formula (more exactly when you switch on cell edit mode by clicking on it or by F2)

image.png

After that it shows the formula, not linked value.

It's something new for me, but Microsoft published the support article about the issue in April this year https://support.microsoft.com/en-ph/help/135506/cell-linked-to-text-formatted-cell-shows-formula-not...

I'd recommend to change your source format (column D and others) in Short List sheet on General, when it works. Please note, you have to reformat you column with links in Qualified back to General. I did that for E column (Short List) and Column M (Qualified) in attached.

 

 

 

 

Got it!

Thanks! I would have never guessed that!

Donald, you are welcome. That was useful for me as well, didn't know about that issue before. On the other hand I never use such cell links.

Hi Sergei,

 

This issue is new to me as well!

The surprise is that this issue exists even in earlier versions of Excel (I've reproduced it in Excel 2010)!

Hi Haytham,

 

I guess that's since we don't use linked cells. But thank you for the sharing, interesting to know that's quite old issue.

@Sergei Baklan I know this solution is several years old at this point, but I just wanted to chime in and say thanks, I had this very same issue come up today and it was driving me nuts. I would have never figured out what the problem was.