Auto Delete Formula and leave resulting value

[Brinny]

I have a multi-sheet Excel 2007 document that I would like to lookup a value based on a start and end date.

This is simple enough, but I want to have the formula auto replaced by the resulting value so that it then becomes static and not dynamic when the contributing values change.

Any ideas?

 

[SkipVought]

Hi,

COPY the range and then Paste Special -- VALUES.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brinny]

Thanks Skip for your response, but I'm looking for an automated solution, the copy/paste combination is very much reliant upon human activity.

My reason for doing this is that there are financial values, specific to dates that I'm sourcing through formula, but these values change and are no longer valid to historic dates.

It's this historic infomation that I"m trying to capture and convert to static values.

Any ideas would be most appreciated. I'm not scared of coding if this is an option

Brinny

 

[SkipVought]

Trun on your macro recorder and record the solution I posted.

If you need help generalizing your code, post for help in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kurupt55]

Hi Brinny

If you want the date to become static when a cell meets a certain condition you could adapt the formula i posted for a similiar problem here.

http://www.tek-tips.com/viewthread.cfm?qid=1561677&page=1

hope this helps

Impossible is Nothing

 

[AnotherHiggins]

As I just posted in that other thread, that's a clever solution, Kurupt, but I wouldn't be comfortable relying on it to store historical data.

If anyone ever comes along and disables iterative calculations you could lose your historical data. IMHO it would be much safer to do as Skip suggests and use a simple macro to replace the formula with the value it returns.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.