Continually increasing the value of a cell, and implying the result on a column

Sorry, for some reason the code from HtmlMaker doesn't seem to work. Anyway, the result would look like this:

\1

If there's something unclear about my problem, I'd be glad to answer further questions!

Okey, let me try to explain it better:

Column F will simply be a list, starting from 0 increasing until the corresponding value in Column G is approximately 0. The increase of the interval could be 10, for instance.

The problem for me is that the value of every single cell in Column G is dependent on all of the values in Column D, which in turn is dependent on the value of cell J4.

So specifying it further:

with the current setup I can manually feed in values for J4, manually write them in the F-column and manually write the corresponding achieved value in the G-column.

As I do want to use many different values for this calculation, I would like to the above described to run automatically. Is this possible?

I'm having trouble following you but it sounds like something that will be simple enough. Can you upload the spreadsheet to this forum (scroll down when formulating your reply and there's a "manage attachments" button).

If that's not possible, can you give me the specific formulas for the columns that you know?

Thanks

ChemistB

Thank you for your answer!

Yes, I'm confident that the solution cannot be very complicated!

I'm attaching a sample of the beginning of the sheet. I've included a description of the problem there as well.

1. Yes, exactly (and additionally there's the condition that "Stat" is set to 0 if previous Stat + Diff < 0). The goal is that this dependence on J4 is replaced with a dependence on the F-column. In other words: I want all the rows to use F3 as criteria when calculating G3. Then, I want all the rows to use F4 as criteria when calculating G4. And so on.

2. Exactly! I.e., first only F3 is applied on column C and column D, to receive G3. Then only F4 is applied on column C and column D, to receive G4. Etc.


Thank you for helping!

Well, because you want to use the same table over and over using different variables, it is a tiny bit complicated You need to use VBA or a macro. Take a look at the example I attached and let me know if this does what you want it to do.

ChemistB

Yes, I start to realize that now.

Looks good from what I can see, but now I should apply it to my real sheet to really know.

That means that
- the "Prod"-column is the B-column, cells B3:B17522
- the "Diff"-column is the F-column, cells F3:F17522
- the "Stat"-column is the H-column, cells H3:H17522
- the "Dis"-column is the the J-column, cells J3:J17522
- the "St.size"-column is the L-column, cells L3:L553 (that is with an increase interval of 100, from 0 to 55000)
- the "Dis/prod -ratio" is the M-column, cells M3:M553
- the input cell "St-size" is P3


How can I apply these changes to the macro?

Thank you for helping!

Okay, I didn't mess with any of the equations within your table so you shouldn't need to change anything there. Here's the modified code with some comments to help you see more clearly what is going on. Let me know if it works.

Please Login or Register  to view this content.

ChemistB

Thank you!

I'm really new to these macros though. I've opened the previous sheet you sent, opened the macro-code within it, and replaced with the new one. The next step is to get this "Calculate series"-button in my real sheet as well, right? How can I do this?

You could run the macro directly through the menus (Tools>Macros>Macros>Run) but the button is a nice touch. I used the Button off of the Forms Toolbar. (View>Toolbars>Forms). It's like the 4th icon on the toolbar. Click and draw your button where you want it. It'll automatically ask what macro you wish to attach to it and give you a list.

This macro is set up to work with the "active sheet" so it will work on all of your worksheets within that workbook provided your ranges are in the same cells?

Good luck.

ChemistB