Accumulate then Redeem (?)

[Tr1gg0R]

Hi Guys,

I'm a fairly basic Excel user so I do apologise if this seems a silly question.

Basically, I have a column which includes Petrol costs - I want the total of those costs to accumulate until I fill up my car with petrol, then the field that includes the accumulated total to reset to zero.

Then the next time I add to the Petrol column I want the total petrol cost field to start totting up again, does that makes sense?

Obviously the issue is that the total field will only need to read from the column from the first entry since the last Petrol fill up.

I don't even know where to start with this, and really hope that my question makes sense!!

Hope someone can help me!

Thanks

[L-Drr]

Do you want to keep the old data, from your previous full tanks?
And how much effort are you prepared to make to redeem the data? Because I'd say just delete your data when you fill your tank...

[Tr1gg0R]

I need to keep the data. In an ideal world I'd just press a 'redeem' button and the total would reset to zero. Of course I realise that it will be difficult for the total field not to take into account the values that have already been 'redeemed'.

[FDibbins]

Hi and welcome to the forum

Have you started putting anything together for this yet? I understand you may not know (yet) how to do what you want, but if we can see what sort of format/layout you are working with, we can probably help with the rest?

[Richard Buttrey]

Hi,

It would be easier if we could see the layout of your worksheet but in its absence does the attached help you with the idea.

[Tr1gg0R]

Hi

Yes I have a basic spreadsheet, I'll try to attach the image.

Untitled.jpg

I want the Petrol Money column to populate the 'Current Petrol' field with a running total, but when I use the current money to fill up I want to reset the 'Current Petrol' field to zero.

[Tr1gg0R]

Hi Richard,

I think that might work, but not sure how to apply it to my SS... any ideas?

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[Tr1gg0R]

FDib = Apologies!

An example SS should be attached!

Would really appreciate it if anyone could help me find a solution to this.

[FDibbins]

Yup, the file came through fine. However, there doesnt seem to be too much data to work with? You have 2 rows of data and the both have data in them? It sounds like you will be entering data (on a daily basis?), row by row, until you fill up again?

If thats teh case, it would help if you could show a "typical" period of data (day, week or month), for us to work with?

(remember, it is totally obvious to you what you have and what you want, but not so obvious to us)

[Tr1gg0R]

Hi guys,

Updated the example spreadsheet, attached. As you can see the 'Current Petrol' field is totting up the 'Petrol Money' column - I want that field to reset to 0 when I fill up with petrol, but the 'used' data to be retained in that same column. I've populated 10 'deliveries' but the spreadsheet will go on indefinitely, so please take that into consideration.

Hope this makes more sense to you guys now.

[Richard Buttrey]

Hi,

As FD has said, whilst you understand your data we (or at least I) don't. How are you defining 'when I fill up with petrol?' By that I mean where on your sheet is this event detected. Perhaps you could add an example which clearly shows a fill up situation and the 'Current Petrol' total sum.

[Tr1gg0R]

Hi Richard,

Apologies. Basically the 'fill-up situation' will probably be different every time. In a real life example, my car needs fuel today, in my example spreadsheet there is £22.84 in the current petrol field, therefore I can put a total of £22.84 in petrol into my car. Say I do that, I then want the Current Petrol field to reset to zero. Then start totting up again from the next entry (into row 14 and so on).

Does that make more sense? It may be that there is no formula that will do this, I realise that it may be quite complex.

Thanks

[Richard Buttrey]

Hi,

But where do you record the fact that you've filled up? I was expecting to see this fact recorded in another column, either as a piece of text e.g. 'fill up' or perhaps a date or amount or gallons - at least some indicator.

Failing that the only other way would be a macro connected to a button which you would click every time you wanted to reset the formula.

i.e. For a bog standard formula to work there must be some 'marker' on the sheet itself that a formula can recognise, otherwise you will need a macro.

Late edit: I'm attaching the workbook with a macro button included.

[Tr1gg0R]

Hi Richard,

A button would be ideal, unfortunately workbook you've uploaded won't work correctly but that may be because my work computer blocks the macro.

I would add data for when I fill up and how much I fill up as well.

Would the/a macro button know how to differentiate between petrol money that has already been 'redeemed' and that which has not?

[Tr1gg0R]

Sorry I've just realised that macros just repeat a process you've taught them - so it wouldn't work as I mentioned above...

I'm sure there must be a way around this - perhaps I could use a forumla that would only take into account values entered after a certain date (i.e. date of last fill up)?

[Richard Buttrey]

Sorry I've just realised that macros just repeat a process you've taught them - so it wouldn't work as I mentioned above...

I'm sure there must be a way around this - perhaps I could use a forumla that would only take into account values entered after a certain date (i.e. date of last fill up)?

Hi,

No you are incorrect. Whilst at their very simplest macros may simply play a recorded series of key strokes, most of the time macros perform much more powerful functionality than that. They can interact with the user, prompt for more information, perform logic tests and take different directions depending on the state of various conditions.

The macro I gave you will do exactly as you have described. It will work out the next blank row and change the formula accordingly every time you click the button. You will of course need to choose to Enable Macros when prompted on opening the workbook.

As I suggested you could use a formula but there needs to be some condition the formula can detect. You mention the date of the last fill up but your current example doesn't record any dates - or indeed any other factor which would indicate a fill up has taken place. That was exactly the point I was making in my previous post. If you want to change your workbook to record such a fact then upload another one. However since the macro version is straightforward and avoids the need to log a 'fill up' personally I'd stick with that.

[Tr1gg0R]

Richard, thank you - that's brilliant.

I don't want to be a pain but even with macros enabled your example sheet doesn't work.

How complicated would it be to add the button myself, could you write down how to do it here? I wouldn't expect you to do that if it takes any amount of time though, you've wasted enough time trying to help me already!

Thanks again, I've added reputation.

[Richard Buttrey]

Hi,

What happens when you open my sheet with macros enabled and why do you think it doesn't work.

You should first see the formula sum in G1 resulting in £22.84. The formula being =SUM(E4:E13)

When you click the button the formula is changed to =SUM(E14:E1048576)

Do you not see this?

The macro in question is at what's called the Module Level and is

Please Login or Register  to view this content.

If you're adding it to another workbook then just copy that macro to a new Module in your workbook. ALT-F11 to go to the VBE, find your workbook name in the Project pane on the left, select the 'ThisWorkbook' item and then from the menu pick Insert Module

Then just double click the Module1 object and in the code area on the right paste the code above. Finally on the worksheet add a button, any shape will do, right click the shape, choose 'Assign Macro' and select the 'FillUp' macro.

[Tr1gg0R]

Richard,

Just checked this on the home PC and it works perfectly! You, my friend, are a hero!

Thank you.