Forum Discussion
Donald McAlister
Sep 15, 2018Copper Contributor
HELP! Excel displaying link instead of linked cell value? (='Short List'!D31, instead of Jeff).
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!!!
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)
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-value
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.
Hi Donald,
Please check in ribbon Formulas tab if Show Formulas is not active
- Donald McAlisterCopper ContributorSergei 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