can a cell have value and formula ?

  • I want to use formula in a workbook. i will have a control worksheet where i will type my workbook will have formulas or values .


    if i type in control sheet range


    A1 = value
    A1=formula


    my workbook should convert all formulas to value.


    if i change to formula all the values should convert to what originally typed formula.



    Remamber this file may also have link to other workbooks.


    Thanks .

  • Are you sure that is what you want to do? If you use one sheet, once you convert the formulas to values you lose the formula. You can either use two sheets that are mirrored and change to the appropriate sheet on a change event. Or you can protect the sheet and hide the formulas so that the user cannot see the formulas you have used.

  • I think that this question is part of this other posting :


    http://ozgrid.com/forum/viewthread.php?tid=136


    Looking at them both you seem to want to put both a formula and a result in the same cell, or else make the cell change from a formula to result(text). This would be some sort of circular reference?
    I am sure that if I say it can't be done someone will show a way. I am sure Excel cannot do this, but there might be a VBA solution somewhere.

  • I need more input???


    are the formulas the same in all columns and cells?


    You can set up a macro to lets say type the formula in row1 or 2 and then fill down. That would be a pretty easy record macro. you could set a button to copy paste special values when done to reserve space.


    I am going to stop talkin because I am not sure what you want.