Userform calculation

[MontySharpei]

Hello,

I hope someone can help. Attached is a sheet Im working on. When the comand button is pressed the userform pops up. The fields are filled out and when it is closed the spreadsheet cells are populated and various calulations are carried out accordingly.

So here is my problem, I fill out the fields and press ok for the spreadsheet to be populated. fine works ok. now i want to add a new line with different numbers. so i go through the userform and fill out the blanks. Now when I press ok and a new line is created in the spreadsheet its cells populated, but my old figures in the old line are replaced with the new ones too !!

can anyone stop it doing this, or alternatively suggest a way i can lock out the line so it cant be further updated.

Thankyou !

[MaximS]

Workfine for me if you talking about adding new line in planned finance sheet. Please confirm if you about Plain Time Cost and Overtime Cost values.

[MontySharpei]

sorry, yes. its in the planned overtime sheet. and its the plain time cost and overtime cost that changes when new line is added.

[Bob Phillips]

Where did you get the formulae from? They are all referring to the same number of rows offset from another line, so they will be the same. Even if you adjust by row, the formula in O10 is changing because of these new formulae.

[MaximS]

I think there was only one thing missing copy/paste as values at the end.
I'v added one extra dynamic range called "a" and one line of code which is doing above.

See example for details.

[MontySharpei]

gentlemen,

As ever you are all saints and have helped me out immensely. If I could trouble you a little further and ask if anyone knows how to solve my next problem. On the attached I have now added two further boxes that will be used to calculate the sum plain time cost and the overtime cost respectively. However when another line is added now how do I go about the value being added to the sum so the total amount is known. to clarify in the attached file cell ar32 currently holds the sum total of cells AR27:AU29. but when another line is added i will need cell ar32 to hold the sum of cells ar27:ar30.

thankyou in advance !

[Bob Phillips]

Change AR32 to

=SUM(AR27:AR31)

and AV32 to

=SUM(AV27:AV31)

[MaximS]

Check attachement for details.

I've changed your formulas and every time you add new row totals will be updated too.


PS. xld your formulas are static so they won't work more than once

[Bob Phillips]

PS. xld your formulas are static so they won't work more than once

Nonsense!

Try and you will see that as each line is inserted, the formula updates because the line inserted is within the SUM range.

[MaximS]

Sorry for my stupid comment.
xld please accept my appologies, you were 100% right.

[MontySharpei]

Thankyou gents, works fantastic !