Automatic Formula to Value Conversion

[workerboy]

Hi,

I have a range of formulas which look up values from another sheet.
Once certain formulas evaluate, I would like those to be static. Is it
possible to have the formula change to just the value at this point,
perhaps by using a macro or some Excel function?

The only way right now I know of is to use the Paste Special option and
just paste the values.

Thanks

 

[Guest]

You can use a macro to do that for you. It will copy and paste special.
Obviously, the macro will need to know where to copy and paste. Without more
detail, no one will be able to really help you though

 

[workerboy]

Hey okay, i'll try and be more specific this time,

I have 2 sheets. In the first sheet from the range A1:A10000 for
example, i want certain data to be filled in.

On sheet 2, i'm going to use a simple formula like
=IF(Sheet1!A1="Bob", Sheet1!A1,"") in cell A1 in sheet 2
=IF(Sheet1!A2="Bob", Sheet1!A2,"") in cell A2 in sheet 2
etc.

Once any cells in the second sheet fill up with values taken from the
first sheet, I want to be able to save those corresponding cells as
values rather than leave them as formulas, as I don't want anyone who
overwrites something in the first sheet (in those specific cells), to
change the values previously stored by the formula in the second
sheet.

Cells that are blank in sheet1 and thus blank in sheet2, should remain
as formulas though.

Thanks ... hope this helps explaining the problem better