-7

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, can anything be done with the formulas or is there another workaround to this issue, 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, 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

Community
  • 1
  • 1
EsatK
  • 33
  • 1
  • 1
  • 6
  • 2
    See: [Why is “Can someone help me?” not an actual question?](http://meta.stackoverflow.com/q/284236) – Scott Craner Nov 21 '17 at 21:11
  • re: *'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.'* You're not doing it right. –  Nov 21 '17 at 21:16
  • @Jeeped Right??? Red flags!!! – dwirony Nov 21 '17 at 21:26
  • @Scott Craner! I have made sure to actually ask a question. The question doesn't ask how to implement a feature, and asks how to accomplish a programming task. I've worded the question in a way that others may search for it. I've clearly explained the scenario and broken down the problem and desired results into as many small steps as I can, and offered further info if I missed anything. So in summary; it seems I've asked a model question and don't see why you've down voted the question or left this link, explain if you will. – EsatK Nov 21 '17 at 21:33
  • @Jeeped Im obviously not doing something write or I wouldn't be here asking for help, if you can't or don't want to help or have no constructive comments then feel free to jog right on. – EsatK Nov 21 '17 at 21:35
  • "I know wrapping time and date stamps in an if statement can stop a similar issue occuring" - that is the answer if you really want to use only formulas and no VBA. – Tim Williams Nov 21 '17 at 21:36
  • It is right in your question `I guess I'm wondering if someone could help me` – Scott Craner Nov 21 '17 at 21:38
  • 2
    ** - I was just turning my comment into a full answer when I noticed I had some replies. After reviewing yours @EsatK, I believe I'll *'jog right on'*. Good luck with your project. –  Nov 21 '17 at 21:40
  • BTW: your answer is here: https://stackoverflow.com/questions/31013853/stop-excel-from-updating-formula-after-a-criteria-is-matched – Scott Craner Nov 21 '17 at 21:43
  • @Tim Williams, this works with date and time stamps but where I have tried this with other data it seems not to. Hence the example in the question, once the discount is applied all previous jobs are recalculated also? I wonder if this is due to me not creating the circular reference referring back to the cell itself like I have with the dates which seem to no longer auto update? Any ideas? – EsatK Nov 21 '17 at 22:41
  • @Scott Craner Answer edited and as for your link, it just reaffirms my wish to go with a non-vba solution as my sheets and workbook are flooded with circular statement Ive purposefully allowed and that work reliably, an example of how I've wrapped a date up =IF(A5="","",IF(F5="",TODAY(),F5)), once the condition is met, unless I click on the cell and hit enter but even then dragging an recopying the formula from the cell above or below fixes this and returns the date desired ie the date when the even occurred (when A5 was no longer ""). – EsatK Nov 21 '17 at 23:22
  • However the formulas I am trying to update pull and combine data and generate a result, for example to price a job the formula is =IF(A9="","",(INDEX(Pricing!$A$3:$H$27,MATCH(D9,Pricing!$A$3:$A$27,0),MATCH(G9,Pricing!$A$2:$H$2,0)))-INDEX(Pricing!$J$3:$AI$32,MATCH(K9,Pricing!$J$3:$J$32,0),MATCH(D9,Pricing!$J$2:$AI$2,0))), do you think the solution should be wrapping this all up in the circular IF statement? – EsatK Nov 21 '17 at 23:22
  • It is the only way with a formula to "lock in" the value. Honestly I would use vba in a Worksheet_Change event, as it is more robust and easier to maintain. – Scott Craner Nov 21 '17 at 23:25
  • @Scott Craner seems like this may well be the case as I want to use a simlar solution on different formulas in different areas of the worksheet. So whilst it has got very complex and detailed, the solution should be something simple that helps turn the flexible result of a formula become anchored to the cell or even replace the formula would be ideal as once the data is there the formula is irrelevant and no longer required. – EsatK Nov 22 '17 at 00:28
  • Perhaps I will look into your linked answer earlier and test it, have you any other perhaps tried and tested solutions to this type of macro as the circular references (thousands of them) worry me as many seem to talk about unreliability and other issues when using macros on a sheet or workbook with enabled itterations and using circular refences? – EsatK Nov 22 '17 at 00:33

2 Answers2

2

It's hard to give you specific advise to implement a non-trivial change on a complex workbook-based pricing application that we have no knowledge of. But here goes...

Make up a new table called CustomerDiscount. That table should have three columns: CustomerID, Date, and Discount. Then rewrite your formulas in your 'transactional' sheet where you are taking booking or whatever, so that they match the CustomerID and Date against the CustomerDiscount table and so pull out the relevant discount that applies after date x.

You can likely find generic examples on Google. The concepts involved here are 'Approximate VLOOKUP' or 'Approximate INDEX/MATCH' so try those search terms in google, and perhaps add something like 'look up next highest date' or similar. I can't give you any more specific advice than that without you having a go at implementing this new 'CustomerDiscount' structure, having a crack at it yourself, and then posting back a much more specific question if you get stuck.

Relying on some trigger to either calculate or not calculate a result is NOT the way to go, because you will always have a nagging fear that something might have been recalculated when it shouldn't have been, and vice versa. You need a audit trail. Without one, you're a sitting duck.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • Something like [this](https://i.stack.imgur.com/faAoK.png). –  Nov 21 '17 at 21:52
  • Yeah...like that but with Excel Tables :-) – jeffreyweir Nov 21 '17 at 21:53
  • @ Jeffreyweir and @Jeeped: Firstly, I appreciate how difficult and vague my question appears, but I was unsure of exactly how much info or what to include, I can provide more if needed just let me know. – EsatK Nov 22 '17 at 00:18
  • However, this is exactly the method Im using to pull the data and calculate costs at the moment, the difference is you worked out if I add a further MATCH and include a date on the customer side this would solve the issue there, but what I didn't make as clear perhaps is the fact that I have the same need in other areas where a date wouldn't be relevant but I am looking to ensure the data remains unchanged after the initial formula event. – EsatK Nov 22 '17 at 00:19
  • for example each row on my master sheet is defined with a booking/job number, if something or someone changes something in one of the sheets I dont want the master sheet which pulls that data to recalculate after its initial event, that way the master data is always safe, does that make any sense? – EsatK Nov 22 '17 at 00:19
  • Does either of you or anyone else know if wrapping the formulas with a circular IF statement like =IF(A5="","",IF(F5="",TODAY(),F5)) would help, and if the reference at the end to F5 is basically treating the formula result from IF(F5="",TODAY(), portion as the result, again if that makes any sense? – EsatK Nov 22 '17 at 00:22
  • 1
    Do not take an event-driven results-overwriting approach to this. See the stuff I've added to my answer. Don't use circular references. Doing so will potentially mask other unintentional circular references and might result in values not updating. If you feel the need for these sort of approaches to 'fix' what you have currently, then what you have currently is a very, very bad design. Redesign. – jeffreyweir Nov 22 '17 at 00:36
  • 1
    If this spreadsheet is integral to your business, I'd consider either buying in the skills to build something simpler than your current approach, using a more database-centric design from the ground up (but still executed entirely in Excel). If I was doing this I would use Excel Tables (aka ListObjects), PivotTables, Data Validation and probably PowerQuery to mash the data together. There would likely be a few INDEX/MATCH combinations, and not a SUMIF or SUMPRODUCT in site. If most of this is gobbledegook to you, then your current skills probably aren't up to the task you've set yourself. – jeffreyweir Nov 22 '17 at 00:42
  • Jeffrey, my skills are definitely and quite obviously not up to the task at hand, and what I have could well be a bad design, I could be explaining it very poorly and doing it an injustice also, and I don't have a budget to work with in order to buy in the skills, I've looked at apps and platforms available and really what Ive put together does what I need quite nicely though I'm sure could always be a million miles better... However, I'm willing to put up a link to a wetransfer so you or anyone else can have a look if you feel you could help out – EsatK Nov 22 '17 at 01:49
  • Nothing that a little reasonably priced online training wouldn't help. If you've got the time. Given the quality of their free online offerings, you couldn't go wrong with any course from Chandoo, Excel Campus, Ablebits, etc. I have no financial interest in any of these, although Chandoo is a social friend of mine. Google will lead you to them. – jeffreyweir Nov 22 '17 at 01:53
1

There are 2 potential solutions: 1. You can go to your formula tab, to the calculation section, and select to "manual calculation" on/off. It's default is on. But if you turn it off, you can change anything and keep the values you have. However, you will have to hit "calculate now" every time you make a change and need it updated, this is very easy to forget to do.

Secondly, you can right click on any of the work book tabs at the bottom and select "Move or copy", and you can make a copy to a blank workbook. You can then save that workbook as their invoice/book.

Also, you can copy a cell, or range of cells, then right click and "paste values" in which the formulas are replaced with the values you are currently seeing. You wouldn't want to do this on your master workbook.

Contact me if you think a macro automating this would be useful. Hopefully some or all of these ideas are useful to you! Please leave a comment if you need further explanation.

Issel
  • 141
  • 6
  • @ Issel... First option for the reason you stated is a no go, moveing and copying I don't see how it will solve the issue other than keep me copies prior and then goes back to the same issues as option one. However copying the cell and pasting its value is will give me the exact desired result but a macro automating this would be extremely useful, I was avoiding a macro as not as familiar with them but seems inevitable. If you could give any further help on this it would be hugely useful to me and I'm sure others too – EsatK Nov 21 '17 at 22:32
  • Just as some further info also, I do have a master sheet where all this the sheets report to and create a huge spread with many columns to allow for the various data pulled from various sheets to be stored. This is compiled by an if statement initially IF(Sheet1!="","" for example and this is also where I would like to use the same macro, that way I can be sure once the formulas calculate and provide results there is no risk of them being changed if other factors are changed. – EsatK Nov 21 '17 at 22:32
  • If you have new information to add that will change other information, it sounds like you could setup your spreadsheets better. Improving your spreadsheets and creating a macro sounds about 2-3 days of work, TBH, it's worth hiring someone to do this for you, and have a high qualtiy work tool is very much worth it. – Issel Nov 22 '17 at 15:32