1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel formula gets removed..

Discussion in 'Business Applications' started by sk0101, Apr 16, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi,

    I included a formula in my cell. (that works fine).

    When I load my excel file the first time, it loads that data based on the formula. OK
    If I erase or change the data that was placed in the cell. The formula also gets removed.

    so when I re-open the excel file again, the formula is not there anymore..

    How do I make the formula always be there.

    Thank you.
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Can you please post the file so we can take a look at it? Are you deleting the cell or clearing the values out? If you delete the cell by right-clicking and selecting delete, then it will mess up the forumla.
     
  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Can you upload the file, so that we can see what you mean.
    No personal data should be loaded. Better ficticious numbers.
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    WE crossed JIm :)
     
  5. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    I am clearing or removing the value in the cell and the formula gets removed as well.
    Thanks
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    When you look at the cell,before you delete the value -

    Is there a value in the cell and a value in the formula bar above the column letters

    or

    Is there a value in the cell and a formula in the formula bar above the column letters
     
  7. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    I see the the value in the formula bar
     
  8. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    sorry I see the Formula in the formal bar and the value in the cell

    attached an example file.

    if you look at the cell, the formula is displayed, and the value in the cell.

    Is there away to keep the formula in place, when I change the value in the cell.

    Thank u
     

    Attached Files:

    • ex.xls
      File size:
      16.5 KB
      Views:
      52
  9. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    I thought that was what you were doing.

    You create a formula to get the results of another cell(s). You do not delete cells with formulas in, unless you no longer need them.

    If you want the information to change in sheet 1, you need to go to the source of the data in Sheet 2 cell A6 and change or delete the data in that cell.

    However, if you delete the data in sheet2 you will see a 0 appear in Sheet 1 cell A6, which I suspect is what your issue is about.

    What you do in a situation like this is to change the formula to the following

    =IF(ISBLANK(Sheet2!A6)," ", Sheet2!A6)

    So that if you delete the data in Sheet 2 Cell A6, the result in Sheet 1 cell A6 will appear to be blank, but the formula is still there.

    Something else that I do, is to use a light background colour for formulas and another one for the data input cells. If I stick to those colours for all the spreadsheets I create, I then know immediately the cells that have formulas in and the cells that I can change.

    See the attached spreadsheet to see what I mean. I have coloured the formula cell a light blue and the input cell a light yellow.
     

    Attached Files:

  10. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi Villan ... Perfect.. Thanks alot for your help :)
     
  11. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    You are very welcome (y):)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704423

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice