why is excel not referencing cells in another worksheet

Question

I have an issue that should be so simple to fix that I feel a bit stupid for even asking, but for the life of me, I cannot find a fix. All I want to do is reference a text cell in a second worksheet and bring the value back to a cell in the first worksheet. This is something I have done more days than I have been around and of course, up until now, it has always worked. Instead of bringing back the text value it simply brings back the worksheet and cell reference, eg Sheet2!C27. This is only happening with a few cells in the first worksheet and many overs work as they should. I have also created a new (third) worksheet and created references to the cells in the second worksheet that will not copy into the first and that works fine indicating that there is no issue with the second sheet. The formatting of all cells is the same and I have also tried, in the first worksheet, copying the rows which contain the cells into which the text does copy into and inserting them under the rows with the 'faulty' cells but they then inherit the issue of the row above. Can anyone shed some light on what the issue may be?

Answer 1

I am experiencing the same issue, literally overnight. When I try to reference cell in a different worksheet but the same workbook, the system is navigating me outside of the workbook to select another workbook. When I traveled that path and selected the same workbook from File Explorer it populated the field with the formula but it does not actually work to bring the field over AND the system tells me the data is coming from another workbook outside of the network.

Because I have an actual client deliverable hinging on completing this effort for the coming week, it would great if Microsoft could figure out what they did and fix it quickly.

Answer 2

Hello there,

I hope all is well!

Does the relevant cells have formatting set to 'Text'? If so, maybe try changing the cell formatting you have the formula in from 'Text' to 'General'. You can check by selecting the relevant cell(s) and pressing and holding together Ctrl + 1 to bring up the 'Format Cells' dialogue box. If this is the case, change the format from 'Text' to 'General'. Once this is done, you can select the cell and press the function key F2 to click into the cell and then Enter to ensure the contents is refreshed after applying the new formatting.

I hope this helps!

With kind regards,

Judith

Answer 3

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

First, you'll want to check the cell format in the second worksheet. Make sure that the cells you are referencing are formatted as text. If they are formatted as something else, like a number or date, then Excel may not display the text value correctly.

Another thing to check is for leading or trailing spaces. If there are spaces in the cells you're referencing, Excel may not recognize them as text. You can use the TRIM function to remove any extra spaces.

You should also check for hidden characters like line breaks or non-printable characters. You can use the CLEAN function to remove these characters.

It's also possible that there is a mistake in your formula syntax, so double-check that you are using the correct cell references and that the syntax is correct.

If you are referencing cells in the same worksheet or in other worksheets that reference cells in the original worksheet, this can cause circular references. Make sure that there are no circular references in your workbook.

Finally, it's possible that your workbook has become corrupted. If none of the above solutions work, you can try creating a new workbook and copying the data and formulas from the old workbook into the new one.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

Best Regards, Sneha

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 4

I have the same or similar problem. I am attempting to reference a cell in one of the tabs (sheets) which I have been done multiple time working with this very worksheet. Now, when I put = and a cell from another sheet in a cell, it grabs a totally unused cell on the original sheet. As I said, this has worked fine with this workbook about 6 times, then all of the sudden, it won't. Any thoughts?

Answer 5

Hello, everyone.

There is a glitch when just using the equal sign to link/reference to a cell in another sheet.
For some reason, when you click on the other sheet, it automatically has you in the search bar in the upper right corner of the spreadsheet.

To bypass this, click on the escape button and then click on the cell you are trying to link/reference to in the sheet. This has been working for me.

Hope this helps.

All the best.

Rafael