Forum Discussion
Cell Displaying Wrong
It sounds like Excel is displaying the formula itself (e.g., =Sheet2!A1) instead of the result of the formula (the value in the referenced cell). This issue is usually caused by one of the following:
Possible Causes and Solutions
- Formula Display Setting is Enabled:
- Excel has an option that toggles between displaying the formulas and their results. If this option is turned on, all cells will show their formulas instead of their calculated values.
- Fix:
- Press Ctrl + (grave accent key) on your keyboard to toggle between showing formulas and showing results. The grave accent key is usually found above the Tab key on most keyboards.
- Alternatively, go to the Formulas tab in the Ribbon and check if "Show Formulas" is enabled. If it is, click it to disable.
- Cell Format is Set to Text:
- If the cell where you are entering the formula is formatted as Text, Excel will treat the formula as a text string and display it as-is, rather than calculating it.
- Fix:
- Select the cell(s) showing the formula.
- Right-click and choose Format Cells.
- In the Number tab, select General or Number and click OK.
- After changing the format, double-click the cell (or press F2 and then Enter) to apply the formatting change.
- Formula Preceded by an Apostrophe:
- If you accidentally typed an apostrophe (') before the equal sign in your formula, Excel will treat the entire formula as text.
- Fix:
- Edit the cell to remove the apostrophe. For example, change '=Sheet2!A1 to =Sheet2!A1.
- Workbook Calculation Mode:
- If the workbook's calculation mode is set to Manual, Excel may not update the cell values automatically.
- Fix:
- Go to the Formulas tab.
- In the Calculation Options group, make sure "Automatic" is selected.
- Accidental Copy of Formula as Text:
- If you copied and pasted the formula from another source, it might have been pasted as text.
- Fix:
- Re-enter the formula manually, ensuring that it's not pasted as text.
Steps to Resolve the Issue
- Check if "Show Formulas" is Enabled:
- Press Ctrl + (toggle formula display).
- Alternatively, check the Formulas tab for the "Show Formulas" button.
- Verify Cell Formatting:
- Right-click the problematic cell(s) > Format Cells > Change to General or Number.
- Ensure Formulas are Entered Correctly:
- Double-check that there is no leading apostrophe and that the formula is typed correctly.
- Check Workbook Calculation Mode:
- Ensure that calculation mode is set to "Automatic."
Summary
By following these steps, you should be able to resolve the issue and have Excel display the results of your formulas rather than the formulas themselves. The text was created with the help of AI.
These steps are nothing more than additional steps to the steps shown by the previous user OliverScheurich . In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem.
My answers are voluntary and without guarantee!
Hope this will help you.
SOOOo, tried that and it made no difference.
It sounds like Excel is displaying the formula itself (e.g., =Sheet2!A1) instead of the result of the formula (the value in the referenced cell). This issue is usually caused by one of the following:
Possible Causes and Solutions
- Formula Display Setting is Enabled:
- Excel has an option that toggles between displaying the formulas and their results. If this option is turned on, all cells will show their formulas instead of their calculated values.
- Fix:
- Press Ctrl + (grave accent key) on your keyboard to toggle between showing formulas and showing results. The grave accent key is usually found above the Tab key on most keyboards.
- Alternatively, go to the Formulas tab in the Ribbon and check if "Show Formulas" is enabled. If it is, click it to disable.
- Cell Format is Set to Text:
- If the cell where you are entering the formula is formatted as Text, Excel will treat the formula as a text string and display it as-is, rather than calculating it.
- Fix:
- Select the cell(s) showing the formula.
- Right-click and choose Format Cells.
- In the Number tab, select General or Number and click OK.
- After changing the format, double-click the cell (or press F2 and then Enter) to apply the formatting change.
- Formula Preceded by an Apostrophe:
- If you accidentally typed an apostrophe (') before the equal sign in your formula, Excel will treat the entire formula as text.
- Fix:
- Edit the cell to remove the apostrophe. For example, change '=Sheet2!A1 to =Sheet2!A1.
- Workbook Calculation Mode:
- If the workbook's calculation mode is set to Manual, Excel may not update the cell values automatically.
- Fix:
- Go to the Formulas tab.
- In the Calculation Options group, make sure "Automatic" is selected.
- Accidental Copy of Formula as Text:
- If you copied and pasted the formula from another source, it might have been pasted as text.
- Fix:
- Re-enter the formula manually, ensuring that it's not pasted as text.
Steps to Resolve the Issue
- Check if "Show Formulas" is Enabled:
- Press Ctrl + (toggle formula display).
- Alternatively, check the Formulas tab for the "Show Formulas" button.
- Verify Cell Formatting:
- Right-click the problematic cell(s) > Format Cells > Change to General or Number.
- Ensure Formulas are Entered Correctly:
- Double-check that there is no leading apostrophe and that the formula is typed correctly.
- Check Workbook Calculation Mode:
- Ensure that calculation mode is set to "Automatic."
Summary
By following these steps, you should be able to resolve the issue and have Excel display the results of your formulas rather than the formulas themselves. The text was created with the help of AI.
These steps are nothing more than additional steps to the steps shown by the previous user OliverScheurich . In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem.
My answers are voluntary and without guarantee!
Hope this will help you.
I tried to reply but it seems it didn't work?? So here is what I said..
----------------------------------------------
I have tried everything suggested by everybody with no joy.
Here is an example of the formula in one of the cells.
I have a "Tab" Named "Final-Info-Sheet" This tab is where the "Text" I want to have shown in the cell within another "Tab" is stored.
Here is that formula being used which did work, then stopped working for some reason. ='Final-Info-Sheet'!G5
Now how did I write the formula? I clicked in the cell I want the results shown in... typed the "=" (no "") then went to the Final-Info-Sheet tab and clicked in the cell holding the text I want referenced. Then I hit the "Enter" key.
That process has always worked before.
Now FYI
This workbook I am turning into a compilation of approximately 5 other workbooks. All this data works perfectly in each of the separate workbooks in which I created the data.
Now I no longer want to have five separate workbooks all interlinked to each other to have all the data calculated for the final sixth workbook which is a set of report forms for all five of the previous ones.
Therefore my goal in what I'm doing is to copy and paste a page out of each of those five workbooks and create a new tab within a singular workbook for each of those previous workbooks pages.
Then go in and interlink the pages or in other words tabs within one workbook instead of having 5 or 6 separate workbooks all interlinking to each other.
I started this project thinking I could do it in a few hours, and it ran into an issue where after 6-months+ of 12-hrs a day creating all of this I had 6 seperate workbooks all interlinked, THEN one of the workbooks stored on the hard drive of my brand new master computer blew up on me a week ago when a co-worker spilled a glass of water on my laptops keyboard and shorted out the motherboard..
Now I have saved all the workbooks in my Dropbox, but within Dropbox they're not interlinked to each other. I intended that they would be, but unfortunately somehow, they got some linked to the workbooks that were on the hard drive and others to the Dropbox saved workbooks.
Therefore to avoid this happening ever again I'm trying to consolidate everything into one singular workbook.
Maybe knowing this might help?
I knew when starting this that copying and pasting would not work because none of the formulas would work and that I knew I would have to interlink tabs rather than workbooks. But once I started trying to interlink tabs I ran into this issue.
I know this is a very big pain in the butt for everybody to figure out but I do appreciate any help anybody can give me
Last FYI
I'll just give you the names of two (out of many) of the tabs I'm currently working on to help make sure you can see what I'm doing.
Here is the tab with the text data within it. The data is stuff like first name and last name home address e-mail address phone number etc, etc… and that tab is named: Final-Info-Sheet
The tab where I am working at and wanting the data to be transferred to is a tab named for the persons name.
In other words, each file within the Final-Info-Sheet as a separate tab where I have a report form created for that person. So, for example, I have a tab name of Bob Smith and within the Bob Smith tab I'm trying to pull the data from his information row within the "Final-Info-Sheet" As in His first name is in cell "G5" within the Final-Info-Sheet tab. His middle name is in "H5", His last name is in cell "I5" in the Final-Info-Sheet Tab etc... etc...
Legally I can't send personal information within a doc to you.
So, I changed all text to Wingding Font, then locked those so you can't read them etc. I did leave all numbers and the separate tabs I'm trying to fix unlocked.
I sure appreciate any help. In fact, I'm doing all this because I just can't afford to hire someone to do it for me at this time. But once this project is completed it's worth a lot of $$$$ to me! So, then I will find and hire someone to fix all this into a more condensed and efficient process. But for now, I've got to get this fixed so I may complete the project and get paid. 🤑
