Formula Problems

Originally Posted by BarbB

I am having a very strange problem with two spreadsheets that I hope someone can help me find the solution to.

What I have is two spreadsheets that each have cells with formulas to display the values in other related spreadsheets. All of the current cells are displaying properly, but now that I'm trying to add cells pointing to other cells in these same spreadsheets, instead of the values displaying in the cell, all I'm getting is the actual formula displaying in the cell.

I've tried copying the formatting of a working cell to the new cell, copying currently working formulas into the new cell and just altering the cell it's supposed to point to, but nothing seems to work.

I've looked into the options of the spreadsheets, and nothing seems amiss (not to mention that there are cells that are working just fine)

I'm baffled. Can anyone help

Thanks

Hi,

the two reasons for Formula showing are usually pressing CTRL ~ (toggle to formula) or the cell is formatted as TEXT.

Rightmouse the cell and check it's format, restore it to General, however, copying a working cell should have done that.

If the cell is not 'Text' then rightmouse the tab and select 'View Code' to ensure there is no VBA code to cause this.

Let me know how you go.
---

Originally Posted by BarbB

Bryan,

Thanks for the response.

I tried the toggle, which did turn everything to formula, then toggled it off, but the offending cell is still not working.

I tried changing the format to General, but it didn’t make a difference. Also, the other working formulas are text format, so that shouldn’t have mattered anyway.

I check for VBA code. There's nothing there.

I've since tried pointing to other cells in other spreadsheets, but the only result I can get is the formula.

Any other ideas?

Thanks
-Barb

Hi,

If you have copied a formula from a working cell to that one, ( ie =A1+A2 ) where A1 & A2 have numbers, and you get the answer, and copy to that cell and it just shows the formula, then I guess:

Edit, Move or Copy Sheet, Make a Copy and copy to a new book

If the problem still exists, remove all of your secret data etc, and post that sheet here as a .zip (in MyComputer, rightmouse and Add to Archive, as a .zip)

---

Originally Posted by BarbB

Yes, I already did these things (two and three times to be sure). Even when I put them in the same Excel file (different tabs) it still does the same thing. This combined file is what I'm uploading.

I dont know how this will display for you since there are other formulas on each sheet that you may not display properly for you.

Thanks so much for your help!

Hi,

nothing showing, any specific cell that you see this in?

note, I presume that you are not referring to (say) Crew07B A44 which shows [I]='\\Navyblue2\hub\Links\[crew07Milwaukee.xls]Crew07M'!A44 [/I]in the fx Formula bar and Rayski/Valdie in the cell ?

---

Select the offending cell(s)
Format as number
Put your cursor in the formula bar
Ctrl+Enter

Originally Posted by Bryan Hessey

nothing showing, any specific cell that you see this in?

---

I'm not exactly sure what this means.

Originally Posted by Bryan Hessey

note, I presume that you are not referring to (say) Crew07B A44 which shows ='\\Navyblue2\hub\Links\[crew07Milwaukee.xls]Crew07M'!A44 in the fx Formula bar and Rayski/Valdie in the cell ?

---

Hi,

Yes, that is what I'm referring to. The formula should show up in the formula bar, but the value should display in the cell. The problem i'm having, is the value is not displaying in the cell the way it's supposed to. The actual formula is what's displaying in the cell too.

-Barb

Originally Posted by BarbB

I'm not exactly sure what this means.


Hi,

Yes, that is what I'm referring to. The formula should show up in the formula bar, but the value should display in the cell. The problem i'm having, is the value is not displaying in the cell the way it's supposed to. The actual formula is what's displaying in the cell too.

-Barb

Hi,

what cells? - there is nothing amiss with the file that you sent, the formulae show in the Fx Formula bar, the values show in each cell.

Which cells do you see any problem with?

---

Originally Posted by Bryan Hessey

Hi,

what cells? - there is nothing amiss with the file that you sent, the formulae show in the Fx Formula bar, the values show in each cell.

Which cells do you see any problem with?

---

The original file I sent may not have had anything other than the original data. (Sorry)

Anyway, I've re-uploaded the the file. You will see the problem in cell A-18. And if you try to plug any other similar formulas in *any* other cells, you'll end up with the same results.

Thanks

Originally Posted by BarbB

I guess uploading the file would help

Hi,

the format of A18 is TEXT

rightmouse, Format cells, and change to general,
then re-enter the formula.

hth
---

Originally Posted by BarbB

I dont get it. I did that at least a half dozen times last week and all it kept returning was the formula.

But I went ahead and tried it yet again, and now it's working.

Oh well, as long as it's working.

Thanks for all your help!

if you enter a formula and than change the cell to TEXT (A19) it has already resoved, and will display that.

If you re-enter A19 it will give you this same problem, change it back to General too.

The display of the '=' (equals) is usually from a 'text' cell.

---

Originally Posted by BarbB

I dont know what happened, but I never got notification of your response. I only just saw it when i got this post. Sorry.

The format 'General' is required for A18/A19 as was suggested in Post #2

---

Originally Posted by Bryan Hessey

The format 'General' is required for A18/A19 as was suggested in Post #2

---

Thanks Bryan. It's working now.

What I still cant figure out is why, when I did the exact same thing more than a half dozen times last week, it wouldnt work.

Oh well. Thanks again.

Originally Posted by BarbB

Thanks Bryan. It's working now.

What I still cant figure out is why, when I did the exact same thing more than a half dozen times last week, it wouldnt work.

Oh well. Thanks again.

because you need to click-in and re-enter the item, just changing the cell format to or from Text has no immediate effect.

---