SOLVED

Cell Reference problem

Copper Contributor

I have referenced a cell to another cell in the same page or in another tab in the workbook, and the cell is formatted to TEXT but I am not getting what is in the referenced cell - I am getting the formula.  Is this as a result of a corrupted file or is there a way I can reformat the cells so that they display what is in the referenced cell, not the formula (e.g. if the numbers in the referenced cell are "200-150-C00" why is it being displayed as +B15 or =sheet1!H5)

10 Replies

@Laura1234 On the Formula ribbon you'll find an icon "Show Formulas". Did you accidentally press it? Press it again and see what happens.

Screenshot 2021-06-22 at 06.31.21.png

Thanks - but it is only showing the formula for some of the cells, not all of the cells. So this is not the problem.

@Laura1234 Did you accidentally type an apostrophe or a space before the =-sign for the formulae that cause the problem?

No - not that either - I think maybe some of the worksheet has become corrupt?

@Laura1234 Can you upload it without disclosing any private or confidential information?

How do I upload in this environment?

@Laura1234 In the box below where you write your reply, you'll see "Drag and drop here of browse files to attach"

 

One other option, perhaps the cells were formatted as text before you entered the formulae.

best response confirmed by allyreckerman (Microsoft)
Solution

@Laura1234 I noticed two cells in the Expenditure sheet (A27 and A51). These contain formula like entries but are displayed as texts. As I mentioned earlier, you must have entered the formula after the cells were formatted as text. To fix the problem, select A27, format at General, click in the formula bar and then Enter to re-confirm the entry of the formula. Now you'll have a working formula. Repeat for A51. Cell A27 will display 0 (zero) as it refers to an empty cell. A51 will display 2000. If there are more cells like this, I trust you can find and fix them yourself.

 

Ahhhh - fixed! thank-you very much!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Laura1234 I noticed two cells in the Expenditure sheet (A27 and A51). These contain formula like entries but are displayed as texts. As I mentioned earlier, you must have entered the formula after the cells were formatted as text. To fix the problem, select A27, format at General, click in the formula bar and then Enter to re-confirm the entry of the formula. Now you'll have a working formula. Repeat for A51. Cell A27 will display 0 (zero) as it refers to an empty cell. A51 will display 2000. If there are more cells like this, I trust you can find and fix them yourself.

 

View solution in original post