Formulas showing in cell???

I keep getting a formula showing in the cell after I edit i
(occasionally).  Show formulas is turned off and edit in cell is on.

How can I make the formula go awaw and simply show the results whic
when edited the results shows correctly?

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 2:51:57 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
570 Views

Similar Articles

[PageSpeed] 22

Hi
probably the cell is formated as 'Text' change the cell format to
'General' and re-enter your formula

--
Regards
Frank Kabel
Frankfurt, Germany


> I keep getting a formula showing in the cell after I edit it
> (occasionally).  Show formulas is turned off and edit in cell is on.
>
> How can I make the formula go awaw and simply show the results which
> when edited the results shows correctly??
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
5/18/2004 3:07:07 PM
along with Frank's suggestion, which i'm in total agreement with
sometimes i've run into the fact that even changing the cell back t
it's native cell format will not always solve your problem. if this i
the case, you may want to think about putting it into another cell, o
putting it into another cell and copy/paste over what you have

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 3:15:05 PM
Hi!

Are you accidentally deleting the = sign at the front of the formula?

Al

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 3:56:02 PM
Reiterating what you and Frank already said plus a couple more

Formula shows in cell instead of formatted value
-- make sure you do not have the cell formatted as text
     select cell,  format, cells,  number (tab),  look at category
-- make sure you are not in the formula view
     tools,  options, view (tab),  make sure formulas is unchecked
-- make sure you don't have a single quote in front of cell, look at
     the formula on the formula tool bar.
-- make sure your formula begins with an equal sign
   Lotus 1-2-3 did not use an equal sign in front, Excel does
   (don't use compatibility options)
-- make sure you do not have a space in front of the equal sign
    if you have a lot of these to fix see TRIMALL macro in
        http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Making a formula(s)  visible is often useful though
-- precede an example with a single quote to make it act as text, or
-- display the formula used in another cell with the GetFormula macro
    in   Show FORMULA or FORMAT of another cell
          http://www.mvps.org/dmcritchie/excel/formula.htm
-- list of formula used  via John Walkenbach's
          Creating a List of Formulas (Tip 37)
          http://j-walk.com/ss/excel/tips/tip37.htm

Formula works but can't be seen:
--  This is a protection feature to hide the formula, it is enabled by
     Format,  Cells, Protection(tab),  turning on Hidden
     and not effective until worksheet is protected with
       tools, protection, worksheet

HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:OAg8ymOPEHA.3096@TK2MSFTNGP09.phx.gbl...
> Hi
> probably the cell is formated as 'Text' change the cell format to
> 'General' and re-enter your formula
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > I keep getting a formula showing in the cell after I edit it
> > (occasionally).  Show formulas is turned off and edit in cell is on.
> >
> > How can I make the formula go awaw and simply show the results which
> > when edited the results shows correctly??
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
>


0
dmcritchie (2586)
5/18/2004 4:06:34 PM
Changing the format to general seems to have worked.  The result of th
formula is a cell formatted as text, so will the attributes transfe
through the general format?  For example if my formula result needs t
be an integer or phone number which general would not cover?  Or, i
the text cell format the only one that causes this problem?
I also did check for: space preceeding the formula, a single quot
preceeding, missing equal sign, and show formulas option in the vie
tab of options.
Thanks!

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 4:31:16 PM
well if you're entering phone numbers, one suggestion would be to ente
the number as such:

8885551212

(shown in the formula bar as such also)

format as Special -> Phone Number, to look like this:

(888)555-1212

and back to what McRitchie was saying, along with the advantages o
having a formula as text, if you'd like to have a short and concise wa
to evaluate that formula, download and install the morefunc add in b
Laurent Longre (here: http://longre.free.fr/english/index.html ) an
use the EVAL function (e.g. =EVAL(A1) - where A1 houses your textua
formula, preceded by a single apostrophe)

--
Message posted from http://www.ExcelForum.com

0
5/18/2004 4:37:28 PM
When changing the format from a number format to at text format
or from a text format to a number format,  you must reenter the
value for the format to take effect.    F2 then Enter
Use of the TRIMALL macro  is one way to effect a reentry.
   http://www.mvps.org/dmcritchie/excel/join.htm#trimall
for more information.

I would never format telephone numbers or zipcodes as numbers
instead I would use text.  Becasue the formats vary from country to
country and you might want to include additional areas.

In fact even in the US with telephone area code no longer optional
I doubt that area code in parens will last much longer in preference to
800-555-1212

The advantage of showing the formula used in another cell is that
you know the formula works.  If you try to evaluate a formula from
text, you don't know that the formula is valid and it will not be adjusted
if you insert/delete rows/columns.  I do have both on my formula page.
--
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

IRS statistics for Tax Forms filed per state are  in Excel files
  http://www.irs.gov/taxstats/article/0,,id=96947,00.html

"firefytr >" <<firefytr.16gn8e@excelforum-nospam.com> wrote...
> well if you're entering phone numbers, one suggestion would be to enter
> the number as such:
>
> 8885551212
>
> (shown in the formula bar as such also)
>
> format as Special -> Phone Number, to look like this:
>
> (888)555-1212
>
> and back to what McRitchie was saying, along with the advantages of
> having a formula as text, if you'd like to have a short and concise way
> to evaluate that formula, download and install the morefunc add in by
> Laurent Longre (here: http://longre.free.fr/english/index.html ) and
> use the EVAL function (e.g. =EVAL(A1) - where A1 houses your textual
> formula, preceded by a single apostrophe).
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
dmcritchie (2586)
5/18/2004 5:33:46 PM
Reply:

Similar Artilces:

macro to move cursor one cell right
If I need to move the cursor to the right to paste a value copied from another sheet , what macro command should I use You don't need to. The Copy Method accepts a Range argument which would be the destination for the paste operation. For example: Worksheets(1).Range("A1").Copy Destination:=3DWorksheets(3).Range("B12") --JP On Sep 22, 3:31=A0pm, Kodak1993 <Kodak1...@discussions.microsoft.com> wrote: > If I need to move the cursor to the right to paste a value copied from > another sheet , what macro command should I use If JP's reply does not do...

how to copy the same cell across different work books into another workbook easily?
i have the daily sales from 1.xls, 2.xls likewise till 31.xls, in a single folder. now i have a final consolidated workbook called final.xls, wherein i would want to copy the same cell across all the workbooks into final.xls(which is again in the same folder) easily,..someway like the fill handle or is there someother way, other than selecting each time the cell to be linked??? can anyone help, this is really breaking my head,,.... -- sageerai ------------------------------------------------------------------------ sageerai's Profile: http://www.excelforum.com/member.php?action=getinfo...

Change a cell's fill color dynamically?
Is it possible to lookup a value in a cell over here and change a cell's fill color over there based on certain criteria? For instance, if all the workdays for a month are listed in column A, is it possible to look up all the Fridays and change the corresponding cell in Column C from whatever color to Yellow? While I'm at it, is it possible to unlock those certain C cells for editing, as well? Thank You so very much. Arlen You can handle the color issue with conditional formatting; you don't need any lookup function. As far as locking/unlocking the cells, you probably ...

Excel 2003 Array Formula in Excel 2007
Hello all, I am moving from Excel 2003 to Excel 2007. One problem I'm having is with the following array formula which works in 2003 but returns a #VALUE! in 2007. Is there something I need to change in my 2007 setup to make this work? {=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K $476)<>0,1,0))-1)} .... I know there are probably more efficient ways of doing this so I'm not looking for better solutions. I'm wondering ... why won't it work? There are 3 array formulas on the spreadsheet and all are returning #VALUE! Thanks in advance! Ben Cacace On ...

I need a different formula that can be copied down
I have a worksheet with numbers 1 through 53 in cells A6 thru A58, respectively. In cell E6 is the following formula that works and updates without having to open the reference file: =INDEX('[01-1209.xls]SFN 119'!$A$13:$C$35,E$64,2) Note: the first two numbers in the formula is the number in A6 written as 01 instead of 1. I want to copy this formula down to row E58; the formual for E7 should be: =INDEX('[02-1209.xls]SFN 119'!$A$13:$C$35,E$64,2) E8 should be: =INDEX('[03-1209.xls]SFN 119'!$A$13:$C$35,E$64,2) etc. How can I rewrite...

How can I check a cell for current date and insert it if blank?
I am modifying the invoice template. I want to create a formaula that checks the date cell for the current date. If a date occupies the cell nothing happens, otherwise, the current date is inserted. Can this be done? Thanks Don Sub insertdate() With ActiveCell If Not IsDate(.Value) Then .Value = Date End With -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Don K" <Don K@discussions.microsoft.com> wrote in message news:85927992-1BA5-4B6A-94A7-AFFCCB607BD7@microsoft.com... >I am modifying the invoice template. I want to create a formaula that >checks >...

Help with autofilling formulas
I need help autofilling a formula similar to this: a1 =c4/(b1-af1)/24 a2 =c5/(b1-af1)/24 a3 =c6/(b1-af1)/24 I would like the C to increase by 1 and the (B1-AF1)/24 to remain constant. Any help would be appreciated. Bryan -- moncara ------------------------------------------------------------------------ moncara's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35649 View this thread: http://www.excelforum.com/showthread.php?threadid=554348 In A1 put this in: =c4/($b$1-$af$1)/24 HTH -- Bearacade ---------------------------------------------------...

How to chart a series of formulas?
Hi, does anyone know how to chart a series of formulas? I currently chart cells in column C. Each cell in column C contains a formula - "=A1+B1", "=A2+B2", "=A3+B3", etc. I would like to do away with column C and just get the chart to calculate the values that are being displayed automatically. How can you do this? Thanks Marcus - Charts aren't that smart, and worksheet ranges are pretty cheap. Compute the data in the worksheet, and plot the column with the formulas. Of course, you could name your ranges (Adata and Bdata) by selecting each range, ty...

how do i extract the formula from a trendline in Excel?
I have a lot of data with an order 6 polynomial trendline. I know I can see the equation used to plot the trendline by displaying the equation on the chart, but is there a way to output this equation to a cell instead? I would like to analyse the data, but copying each equation for each set of data into cells (and having to rewrite them as formulae that excel will recognise) will take a long time. Thanks. If it's a poly fit, use the LINEST worksheet function: http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm If it's more complex, try Tushar's approach: www.tushar...

Protecting cell contents
I have about 5 cells on my sheet that contain formulas which reference other sheets. I therefore want to prevent users from accidentally changing or deleting these 5 formulas. They are free to modify any other data on the sheet. How can I preserve these 5 cells which cointain formulas without using the "Protect Sheet" options??? BTW, users have the ability to protect and unprotect this sheet at any time with their own passwords, so it seems like I need another way to protect my formulas. I mean, once the user un-protects the sheet, they are able to delete anything an...

array formula for max of Pearson correlation
HI, I=B4d like to "compact" the following column in a single cell value using array formulas, if that is possible. The columa A is filled with results of a certain function, and column B has the Pearson correlation of that column in this way. b1: =3Dpearson(a1:a5,a2:a6) b2: =3D pearson(a1:a5,a3:a7) .. .. b100=3Dpearson(a1:a5,a101:a105) Then, what I really need is the max of those b cells, so c1=3Dmax(b1:b100). The question is, can I avoid the creation of column b, with something like : c1=3D{max(pearson(a1.....))}, if it is possible I can=B4t find the right sintax... Thanks ...

Show maximum date in a query/report
I am having trouble with a query/report that I run that should show the last payment date that a tuition payment was made. It works for most records but for the ones where the tuition payment is more than it was the previous month then it it showing the payment date and tuition payment from that previous month. For example the records are PayDate of 2/3/2010 and TuitionPayment of $45.00 PayDate of 3/2/2010 and TuitionPayment of $80.00 I would expect the query/report to show the 3/2/2010 payment but it is showing the 2/3/2010. Like I said most of the accounts are showing the c...

Cell Styles on Ribbon
The context window that should "pop-up/out" when I click on Cell Styles on the Home tab is "locked" on to my ribbon. Normal, Bad, Good, etc. is on one line and Check Cell, Explanatory, Input, etc is on the second row. I looks like something that you would do if you wanted to modify a ribbon. What is your question? -- HTH Bob "Stephen J" <Stephen J@discussions.microsoft.com> wrote in message news:D1F8E067-FD46-442F-81BF-9F28AFE465BE@microsoft.com... > The context window that should "pop-up/out" when I click on Cell Style...

IF clause for a range of cells
The following formula works well for me but is there a way to make it shorter by defining a range of cells, rather than individual cells, in the IF part? =SUM(G8:AJ8)+IF(G8="",4)+IF(H8="",4)+IF(I8="",4)+IF(J8="",4)+IF(K8="",4)+IF(L8="",4)+IF(M8="",4)+IF(N8="",4)+IF(O8="",4)+IF(P8="",4)+IF(Q8="",4)+IF(R8="",4)+IF(S8="",4)+IF(T8="",4)+IF(U8="",4)+IF(V8="",4)+IF(W8="",4)+IF(X8="",4)+IF(Y8="",4)+IF(Z8=&quo...

Using add-type to show a file's property page
I am trying to use powershell 2.0 to show a file's property page by using add-type. The code below is the closest I have got so far but haven't got a clue how to get it working I can't pretend I know what I am doing with this, I have just tried to hack it together from various sources. I certainly know nothing about C# $source=@" public static void ShowFileProperties(string Filename) { SHELLEXECUTEINFO info = new SHELLEXECUTEINFO(); info.cbSize = System.Runtime.InteropServices.Marshal.SizeOf(info); info.lpVerb = "properties"; info.lpFile = Filename; ...

Selection List box in a cell?
How do I use one sheet to allow the user to enter text in each cell of a column. Then in another sheet convert it to a selection list in one cell ? Hutch, You can accomplish that using Data|Validation|Allow List. The List reference should be to a Named Range where the user inputs their data. You should be able to use a dynamic range that would allow for a variable list length using something similar to the formula below as the Name Definition =OFFSET($A$1,0,0,COUNTA($A:$A) To create the named range. (This formula would assume that there is no row heading and that there would be no o...

how to show bmp picture from OnInitDialog()
hi i have to show the bmp picture while OnInitDialog(). my code is working fine on button click but when i insert it OnInitDialog() it does not show the bmp. could anybody plz help me Mujtaba "Mujtaba" <tomujtaba@hotmail.com> wrote in message news:8f4f92aa.0406112313.6e2d9d1e@posting.google.com... > hi > i have to show the bmp picture while OnInitDialog(). my code is > working fine on button click but when i insert it OnInitDialog() it > does not show the bmp. > > could anybody plz help me Without knowing what you do in OnInitDialog it's a bit of a gue...

why cut and insert cells only works randomly?
It seems that cutting and inserting cells in the spreadsheet, errors every other time and locks the excell spread sheet... Hi Tony, You'll have to be more specific, but you might find the answers to such problems as inserting rows, using OFFSET with formulas. And you will probably find why extending formulas and does not work for you. -- if any of those are the problem. http://www.mvps.org/dmcritchie/excel/insrtrow.htm I don't know what you mean by locking the sheet, have you turned on sheet protection or have merged cells in your copy.. --- HTH, David McRitchie, Microsoft...

copy cells which are horizontal to verticle
Hi, How do I copy a set of cells which are horizontal to verticle or vice versa? For example: A B C D E F G H I J 1 a 2 b 3 c 4 d 5 6 To become A B C D E F G H I J 1 a b c d 2 3 4 5 6 Thanks. You could copy the selection and then use paste special and check the transpose check box on the bottom right corner of the dialog box. Ed -- nuver ------------------------------------------------------------------------ nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10036 View this thread: http://www.excelforum.com/show...

Formula for: Running-total for bottom portion of column?
[Excel 2002] I'm looking for the formula that will calculate a runnin total for a column from a point/cell halfway down that column extending down to-and-including the most recent entry. Obviously, for an entire-column running total, th "=SUM(column_name:column_name)" would work, but I don't want to includ the first -n- group of cells. Thanks for your time & attention to detail ;-} Terry ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Avail...

Report does not show
One of our users complained last night that the report was not showing the data she entered into the form. I went to verify and found that the "report" was in the middle instead of showing at the end like it usually did. Please help and I hope I am explaining this right. It is very frustrating for the users. You may need to clarify a bit. You wrote that "the 'report' was in the middle instead of the end..." Do you mean the data the user entered was in the middle of the report as opposed to the end of the report where it is normally? If that is the case, I woul...

How to get Budget to show non-budget transactions
I'm using Microsoft Money 2007 Deluxe and the Advanced Budget. I want the Budget to show me totals for non-budgeted expenses. For instance, if I enter a transaction under 'Vacation' and I don't have a Budget item for Vacation for the current month, then I can't see that on the Advanced Budget page. Only if I add a Budget item for Vacation am I able to see that affect my total. I guess what I would like is an entry in the Budget for "non-budgeted expenses". Thanks, Stephen ...

How do I add cells from different worksheets?
I am trying to add totals from 5 separte worksheets on the last worksheet which I am calling a summary page. Can anyone share with me how to do this? I tried the Sum button and then holding control as I click on each total on the different worksheets but it doesn't seem to be working. Assuming the amounts are on the same cell on each worksheet use a formula like this: =SUM(Sheet1:Sheet5!A1) Otherwise you'd need a formulas like this: =Sheet1!A1+Sheet2!B2+Sheet3!C3+.. -- Jim Rech Excel MVP "ExcelErin" <ExcelErin@discussions.microsoft.com> wrote in message news...

applying dropdown list entry to multple cells
Is there a way to get the entry from a dropdown list to apply to multple cells? All the selected cells have the same list set in their validation rules, but simply selecting many cells and picking from the list in one does not work. Select your cells, select the value on one drop down, then press F2 and Ctrl-Enter. HTH, Bernie MS Excel MVP "twild" <twild@discussions.microsoft.com> wrote in message news:5DCBC32F-624F-4D2E-B9DD-536BA723F453@microsoft.com... > Is there a way to get the entry from a dropdown list to apply to multple > cells? All the selected cells ha...

Condition based on multiple cells
I would like to look at 3 cells: A6, C6, E6. If all 3 of these cells are blank, I would like to return a blank cell. If any of the 3 cells has a value, I would like to return the average. I am familar with how to do this based on 1 cell, but confused as to how to get it to work with 3! =IF(AND(A6="",C6="",E6=""),"",AVERAGE(A6,C6,E6)) WAR wrote: > I would like to look at 3 cells: A6, C6, E6. If all 3 of these cells are > blank, I would like to return a blank cell. If any of the 3 cells has a > value, I would like to retur...