How to make a formula result static

[Esat Karahasan]

Or perhaps another way to look at it would be how to make a formula result static...

The problem: I have created an  invoicing/booking solution for my business using excel sheets, I have many sheets such as delivery addresses, client data and addresses, bookings, jobs, invoices and so on. There are many many formulas which do different things at different times, for example, if a client books a service for an address the sheet will auto calculate the cost of the service through a series of formulas and IF statements. But if I change any of the data a pricing formula relies on it will recalculate all formulas relating to that data change, an example a customer books today at $100, on their next booking I give that customer a permanent discount of $25 on all future bookings, once I add the discount against the customer, the pricing formulas recalculate all the formulas which could cause obvious accounting issues.

I know wrapping time and date stamps in an if statement can stop a similar issue occuring and that their is also macros that can be written to time stamp on an event, I guess I'm wondering if someone could help me with the formulas or a workaround this issue would be great as ive almost got all the functionality I need without using any macros which in itself is an achievement but on the other hand if a macro is the only way if there is anyone that could help with any options it would be great.

In short can anyone offer any solutions to making formula results static to those cells, ie rewrite the formula with the result itself so the result is permanent and therefor only editable manually.

I havent included any screenshots as I couldnt really see what would be relevant, but if there are any bits you'd like to see or have more info on to help answer feel free to ask

[#2]

Hi Esat,

It does sound like you'd be better off with some VBA to copy and paste historic transactions as values into a table / database.

It's a difficult query to answer without spending a fair bit of time getting into the detail.

Could you add a date field to your customer pricing table (e.g. a new row with a new date each time the date changes) and then have your formula bring back the relevant price based on current date? 

[#3]

I could add a date and that would work perfectly for that single occurance, but there are many other possible changes that could cause problems hence why Im trying to find a way to at least make the record generated "static" or basically replace the formula with the answer. I guess pasting the value is the result im after, just how to get to it in an automated way is the issue.

---

[#4]

Unfortunately I'm not going to be able to help with this as it seems to be more of a project than a quick answer. Hopefully another kind soul on the forum can assist.

Sorry I can't help further.