|
Formula snafu
I am baffled. I received a challenge to edit the date in one column. It's a
number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
|
Formula snafu
Here's befo
Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) Click Tools/Options on Excel's menu bar and then click the View tab on the dialog box that appears. In the bottom section (labeled "Window options").... is there a check mark in the Formulas entry? If so, uncheck it. Rick |
Formula snafu
other formulas in the same cell work OK?
If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
To answer Rick Rothstein reply; the formula box is not check.
I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
Are the formula cells formatted as text? if so reformat as General and if
this does not work multiply by 0 e.g. right(d3,4)*0 Peter "El Bee" wrote: To answer Rick Rothstein reply; the formula box is not check. I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
Change the cell format to General then F2 + ENTER.
Gord Dibben MS Excel MVP On Mon, 12 Nov 2007 11:21:00 -0800, El Bee wrote: To answer Rick Rothstein reply; the formula box is not check. I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
Gord,
Your suggestion fixed the problem; but what does the F2+Enter really do? Thanks to all for your suggestions and time. Larry "Gord Dibben" wrote: Change the cell format to General then F2 + ENTER. Gord Dibben MS Excel MVP On Mon, 12 Nov 2007 11:21:00 -0800, El Bee wrote: To answer Rick Rothstein reply; the formula box is not check. I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
Formula snafu
F2 tells excel that you want to edit the contents/formula of that cell.
Hitting enter tells excel that you're done with the edit. So excel sees that it should look at that cell and since it starts with an equal sign and is formatted as General (not Text), that it should re-evaluate it as a formula. El Bee wrote: Gord, Your suggestion fixed the problem; but what does the F2+Enter really do? Thanks to all for your suggestions and time. Larry "Gord Dibben" wrote: Change the cell format to General then F2 + ENTER. Gord Dibben MS Excel MVP On Mon, 12 Nov 2007 11:21:00 -0800, El Bee wrote: To answer Rick Rothstein reply; the formula box is not check. I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? -- Dave Peterson |
Formula snafu
That's what I would have posted<g
Thanks Dave. Gord On Tue, 13 Nov 2007 11:32:07 -0600, Dave Peterson wrote: F2 tells excel that you want to edit the contents/formula of that cell. Hitting enter tells excel that you're done with the edit. So excel sees that it should look at that cell and since it starts with an equal sign and is formatted as General (not Text), that it should re-evaluate it as a formula. El Bee wrote: Gord, Your suggestion fixed the problem; but what does the F2+Enter really do? Thanks to all for your suggestions and time. Larry "Gord Dibben" wrote: Change the cell format to General then F2 + ENTER. Gord Dibben MS Excel MVP On Mon, 12 Nov 2007 11:21:00 -0800, El Bee wrote: To answer Rick Rothstein reply; the formula box is not check. I'm not copying the formula from one cell to another. In my example cell E2 just had the formula manually entered but it does not display the information from cell D2; it only displays the formula. I went to a colmn further to the right; column J2 and manually entered the same formula and it wouldn't show the correct information either. I had a cell, C2 which contained a name so I placed a formula in K2 that said "=C2" and the name was displayed. I then retyped the same formula in K2 "=C2" and the name would not display; only the formula I typed in. "bj" wrote: other formulas in the same cell work OK? If you are copying and pasting the formula , select the "=" and replace it with a new "=". "El Bee" wrote: Don. Here's befo Column D Column E 1280-28101 Here's After Column D Column E 1280-28101 =right(D2,4) ( this is what is displayed in the cell E3) ( should display 8101 ) "Don Guillett" wrote: Before/after examples? -- Don Guillett Microsoft MVP Excel SalesAid Software "El Bee" wrote in message ... I am baffled. I received a challenge to edit the date in one column. It's a number that is formatted as a string. I figured no problem so I entered a formula in an adjacent cell "=right(D3,4)" and the only thing displayed is the formula (in the cell)! I tested a different column and got the same thing. So I changed the formula to point to a cell that had a name in it. The name was then displayed in the new cell. I then went back to the same cell with the formula that pointed to the persons name and re-entered the formula and guess what, it now only displays the formula and not the persons name. Is there some security issue I'm dealing with in this spreadsheet if so how do I turn it off? |
| All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com