How can I keep keep a running total of ever changing values?

[imdum]

What I have:

A) 2 lists of 100 different items. Both lists have exactly the same items.
B) At the push of a button, a random amount is assigned to each item on the first list.
C) On the next push of the button, all of the amounts in the first list are erased and then a new set of numbers appear for each item. This happens every time the button is pressed.
D) The second list remains unchanged.

What I want to happen:

A) On the first push of the button, I want all the amounts to be transferred to the appropriate items in the 2nd list.
B) On each future button press, I want the amounts that show up in the first list to be added to the amounts that are already in the second list.
C) When the sheet is closed and then re-opened, it will show a running total of all the past numbers in the second list for each item.

What do you think?

Thanks!

[Lupp]

What do you think?

I think nobody can help you with an answer of a few or even a few dozen lines. You will have to study the programming for Calc using its API. An estimate of what's to do you may get from the attached example. Don't hesitate to drop the project if you feel the learning and programming won't pay.

[imdum]

Thanks for your efforts.

"Don't hesitate to drop the project if you feel the learning and programming won't pay."

And thanks for that handy tip

Just thought I'd let you know that that project you helped me with I have a real working version of it, I just have a lot of cleaning up to do on it. Some areas I didn't think were going to be possible but were. Maybe not as beautiful as an expert (who had done a lot of studying) could have done it (if the expert had the idea) but somehow it works.

I'm not quite sure what to make of your example but I have figured out something else that's pretty klunky but it somehow seems to work.

Your a good guy, Lupp, and I really do appreciate your efforts. Maybe one day you well get me studying this!

[Lupp]

...but I have figured out something else that's pretty klunky but it somehow seems to work.

The forum is not one-way. Would you mind to share your solution?

As far as I understood your original question, and considering the fact that you posted in the "macro" section, I thought the near-solution I posted would easily allow for the adaptions you need to get exactly what you want. To pass parameters to a 'Sub' is always problematic, but to hard-code them is more, imo. Thus I decided to demonstrate a way using a dedicated cell range for the parameters. Another fact that I did not yet mention was that I took for assured that the second list of items is in the same order as the first one. If not, the additional steps might better be done based on standard functions (VLOOKUP e.g.) in a sheet than by a user Sub.

[Villeroy]

A) On the first push of the button, I want all the amounts to be transferred to the appropriate items in the 2nd list.
B) On each future button press, I want the amounts that show up in the first list to be added to the amounts that are already in the second list.
C) When the sheet is closed and then re-opened, it will show a running total of all the past numbers in the second list for each item.

What do you think?

If this is meant to be some kind of inventory or invoicing, this is the wrong approach.

[imdum]

Lupp

Basically what I did was have a button I clicked and then 3 columns, A, B and C. Lets just say for this example I had only one row. The three columns start at zero.

I recorded a macro with where, at a click of a button, first a random number is placed in column B and then column A and B are added (by a formula in column C), which gives a result in column C.

The macro then copies what is in column C, "Paste Specials" it into column A, over what was previously there, and so, at the first click of the button, you have in Column A the result of the total of what was in Column A plus Column B.

So at the first click of the button you would have in column A the random number that was placed in column B.
Then at the end of each click after that, you would have whatever was previously in column A plus whatever got placed in column B on that click.

So lets say you clicked the button 5 times and got as random numbers 1, 2, 3, 4, and 5. It would have been like this:

A B C
0 0 0 (this is before the button is clicked)
1 1 1
3 2 3
6 3 6
10 4 10
15 5 15

(Note: this would only be row 1 for each of those lines, it wouldn't be row 1-6 as this example seems to suggest).

For what I'm trying to do, what is in Column A is what's important. Columns B and C are irrelevant, except that they get me whats in column A.

The one problem I have with this is that eventually this thing will mess up and and paste the result where it's not supposed to be pasted, and usually over other info in the spreadsheet. In my situation this is not disastrous, because I found that if I save each click I can go back and do it again, but still that makes it far from perfect. But PLEASE, if anyone uses this DO NOT expect it to work perfectly every time. Expect it to mess you up really horribly, because that's exactly what I saw it do. Several times.

What I'm saying is, this almost certainly will NOT solve your problem, and will probably likely give you EVEN WORSE problems.

Villeroy. No, its not inventory or invoicing, or anything remotely related to any of that.

[robleyd]

This smells a bit like the XY Problem

Perhaps if you tell us what the problem is that you need this button clicking solution for, someone might be able to suggest an alternative approach.

[imdum]

Ok, the "XY problem", which I've never heard of before. I see what your talking about.

In order to keep the question simpler, I just put in random numbers from the "randbetween" function.

What I'm trying to do is build a "slot machine" that relies on 100 different results from the random number generator. The results of these numbers are what give the "payoff" for each situation.

What column B really is is the result of each payoff (not the random number I mentioned in the question).
What column A is is the running total of how much has been paid off for each item.

Don't know if that will help and make a difference for a possible solution, but anyway, that's pretty much what it's about.