How to Dynamically Add Rows based on value of last row

[snerheim]

I've been searching the boards for hours on this and figured that it is time to just ask. I have a feeling that this will be a very simple answer.

I am working on a spreadsheet to determine the number of monthly payments until a debt is paid off. The monthly payments change based on several values. I can easily find the number of payments needed to pay off the debt.

However, it doesn't display real nice. I have to drag the row of equations down manually and then "eyeball" where the payments have reached the right level. This results in many unecessary rows after the final payment has been made.

Is there a way to dynamically "drag" the equations down until a certain field value is reached and then "stop dragging" the row? This would result in the final row of equations being the "answer" and not displaying any more information.

Thanks in advance.

-steve
This message was edited by snerheim on 2002-08-23 13:53

 

[Swamp Thing]

Assume that you have a column that has 100 cells. You don't really need all 100 because some condition is satisfied by the 75th one which tells you to 'stop dragging down'. For example, let's say the condition is when the cell is >34.

What you can now do is leave the existing column alone, but add another one containing an IF formula in each cell.

=IF( (A1>34), A1, "")

This will give you a new column with just the right number of cells as per your condition.

Does that help?

 

[Aladin Akyurek]

On 2002-08-23 13:52, snerheim wrote:
I've been searching the boards for hours on this and figured that it is time to just ask. I have a feeling that this will be a very simple answer.

I am working on a spreadsheet to determine the number of monthly payments until a debt is paid off. The monthly payments change based on several values. I can easily find the number of payments needed to pay off the debt.

However, it doesn't display real nice. I have to drag the row of equations down manually and then "eyeball" where the payments have reached the right level. This results in many unecessary rows after the final payment has been made.

Is there a way to dynamically "drag" the equations down until a certain field value is reached and then "stop dragging" the row? This would result in the final row of equations being the "answer" and not displaying any more information.

Thanks in advance.

-steve
This message was edited by snerheim on 2002-08-23 13:53

Care to provide a small sample that illustrates your question?

 

[snerheim]

Sample:
I'm having a party and we need to finish off a keg. The goal is drink 150 beers. Column A lists each guest by number, Column B lists their capacity. Column C lists our total so far. We need column C as a running total so we can post the schedule.


Guests Beers Total So Far
1 6 6
2 9 15
3 4 19
4 3 22
5 8 30
6 7 37
7 8 45
8 4 49
9 6 55
10 3 58
11 8 66
12 0 66
13 9 75

I want the spreadsheet to automatically extend the calculations until 150 is reached. Once column C >= 150, no more rows should contain data.

My real case involves 30 odd columns and will result in 2 to a X rows. I want it to be flexible enough that I don't have to drag the rows out manually.

 

[Aladin Akyurek]

On 2002-08-24 07:03, snerheim wrote:
Sample:
I'm having a party and we need to finish off a keg. The goal is drink 150 beers. Column A lists each guest by number, Column B lists their capacity. Column C lists our total so far. We need column C as a running total so we can post the schedule.


Guests Beers Total So Far
1 6 6
2 9 15
3 4 19
4 3 22
5 8 30
6 7 37
7 8 45
8 4 49
9 6 55
10 3 58
11 8 66
12 0 66
13 9 75

I want the spreadsheet to automatically extend the calculations until 150 is reached. Once column C >= 150, no more rows should contain data.

My real case involves 30 odd columns and will result in 2 to a X rows. I want it to be flexible enough that I don't have to drag the rows out manually.

What follows is I'm sure is not the way you want it...

Let A2:C15 house the sample you provided with labels in A2:C2.

( 1.) Activate Insert|Name|Define.
( 2.) Enter BigNum in the Names in Workbook box.
( 3.) Enter the following in the Refers to box:

9.99999999999999E+307

( 4.) Click OK.

In A1 enter:

=MATCH(BigNum,B:B)

In B1 enter: 50 [ the preset sum value ]

In C3 enter:

=IF(N(C2)>=$B$1,"",SUM(C2,B3))

In C4 enter:

=IF(C3>=$B$1,"",SUM(C3,B4))

This formula is the one that must be copied down by double clicking on the fill handle (little black square) in the lower right corner of C4 or by just dragging it down.

The foregoing is I believe is the precisely the action you'd rather not to take.

You'll possibly get, now that you have a better description what is desired, a self-contained VBA code that does this or one (which I'd prefer) that reads the value in A1 and copies C4 (the formula that this cell houses) up to the row A1 has determined.

We'll see what will come...

See the figure...

DynAdd.xls
	A	B	C	D
1	15	50
2	Guests	Beers	TotalSoFar
3	1	6	6
4	2	9	15
5	3	4	19
6	4	3	22
7	5	8	30
8	6	7	37
9	7	8	45
10	8	4	49
11	9	6	55
12	10	3
13	11	8
14	12	0
15	13	9
Sheet1

This message was edited by Aladin Akyurek on 2002-08-24 08:18

 

[snerheim]

Thanks for the demo. I'm doing something similiar now. This prevents data from showing in the result column once the answer has been achieved.

I think you are right in that I'll be better off with VBA code that continues to apply and progressing equation until a result is achieved.

I'll get off my lazy can now and learn to write VBA scripts (I was hoping it wouldn't come to that).

Thanks,
Steve

 

[gwkenny]

I've actually recently done a mortgage calculator that just does that.

Put in you 3 out of 4 of the following: Interest, Payment, N Periods, Loan amount. The spreadsheet then displays the cashflow. Formulas go all the way down the sheet but the cashflow stops at the last payment. Just have to plan your formulas out ahead of time so if there is no payment in that period it stops. If you plan it out correctly you can even have sums appear right after your last payment. Then use conditional formatting to place a border before and after the sum row as well as bold it. Kind of bummed that conditional formatting does not have a double line border (XL 2000). Just remember to format the Zeros to not show.

I'll pass you one of my inprogress sheets if you want. Just email me. It's not pretty but it works

g-
This message was edited by gwkenny on 2002-08-25 10:45

 

[Aladin Akyurek]

On 2002-08-25 10:43, gwkenny wrote:
I've actually recently done a mortgage calculator that just does that.

Put in you 3 out of 4 of the following: Interest, Payment, N Periods, Loan amount. The spreadsheet then displays the cashflow. Formulas go all the way down the sheet but the cashflow stops at the last payment. Just have to plan your formulas out ahead of time so if there is no payment in that period it stops. If you plan it out correctly you can even have sums appear right after your last payment. Then use conditional formatting to place a border before and after the sum row as well as bold it. Kind of bummed that conditional formatting does not have a double line border (XL 2000). Just remember to format the Zeros to not show.

I'll pass you one of my inprogress sheets if you want. Just email me. It's not pretty but it works

g-
This message was edited by gwkenny on 2002-08-25 10:45

How does this differ from what I suggested?

 

[gwkenny]

lol.

Not a whit!!!

I'm pretty new here, but I've stated a few times that I'm pretty lazy (and I am!!!).

I generally read the first post in a thread then continue from the bottom up. I just read his answer that he was going to go ahead and do coding instead of working with formulas. So I just posted. Never even really looked at your solution If I did I wouldn't have posted. Err, maybe I would have, but definitely not so much

g-

 

[Aladin Akyurek]

On 2002-08-25 11:00, gwkenny wrote:
lol.

Not a whit!!!

I'm pretty new here, but I've stated a few times that I'm pretty lazy (and I am!!!).

I generally read the first post in a thread then continue from the bottom up. I just read his answer that he was going to go ahead and do coding instead of working with formulas. So I just posted. Never even really looked at your solution If I did I wouldn't have posted. Err, maybe I would have, but definitely not so much

g-

His problem is that he wants the essential formulas to be copied down automatically and dynamically until a condition is met. Thus not manually and not to a preset number of rows. I believe a simple VBA code can do that.

Your procedure for deciding to reply by reading the OP's answer to a previous (someone else's) reply is a bit risky: The OP might be just wrong about the adequateness of the previous reply.