Auto add/delete rows based on specific cell value.

[Sean15]

Hi:
I want to add rows to table below based on valued in G2. So, if G2 has 61, table should have 61 rows starting from E10, if 41 in G2, table should have 41 rows, etc.

I also want to keep the subtotals as shown in F61 and G61 but subtotal should adjust up/down based on value in G2. I have searched online but can’t find codes that does that. Also, I read that AI tools have just as much VB skills as I do so I better ask the professionals.

Can someone help?

PV of lease pymts.xlsm
	E	F	G
1
2		Number of Periods	51
3
4
5
6		Annual Rate	9.00%
7
8
9	Period	Cash	Present Value
10	1	$237,651.19	$235,882.07
11	2	$237,651.19	$234,126.12
12	3	$237,651.19	$232,383.25
13	4	$237,651.19	$230,653.35
14	5	$237,651.19	$228,936.33
15	6	$237,651.19	$227,232.09
16	7	$237,651.19	$225,540.53
17	8	$237,651.19	$223,861.57
18	9	$244,780.72	$228,860.96
19	10	$244,780.72	$227,157.28
20	11	$244,780.72	$225,466.28
21	12	$244,780.72	$223,787.87
22	13	$244,780.72	$222,121.96
23	14	$244,780.72	$220,468.45
24	15	$244,780.72	$218,827.24
25	16	$244,780.72	$217,198.26
26	17	$244,780.72	$215,581.39
27	18	$244,780.72	$213,976.57
28	19	$244,780.72	$212,383.69
29	20	$244,780.72	$210,802.67
30	21	$252,124.14	$215,510.42
31	22	$252,124.14	$213,906.13
32	23	$252,124.14	$212,313.78
33	24	$252,124.14	$210,733.28
34	25	$252,124.14	$209,164.54
35	26	$252,124.14	$207,607.49
36	27	$252,124.14	$206,062.02
37	28	$252,124.14	$204,528.06
38	29	$252,124.14	$203,005.52
39	30	$252,124.14	$201,494.31
40	31	$252,124.14	$199,994.35
41	32	$252,124.14	$198,505.56
42	33	$259,687.87	$202,938.69
43	34	$259,687.87	$201,427.98
44	35	$259,687.87	$199,928.52
45	36	$259,687.87	$198,440.21
46	37	$259,687.87	$196,962.99
47	38	$259,687.87	$195,496.77
48	39	$259,687.87	$194,041.45
49	40	$259,687.87	$192,596.98
50	41	$259,687.87	$191,163.25
51	42	$259,687.87	$189,740.20
52	43	$259,687.87	$188,327.74
53	44	$259,687.87	$186,925.80
54	45	$267,478.50	$191,100.32
55	46	$267,478.50	$189,677.74
56	47	$267,478.50	$188,265.74
57	48	$267,478.50	$186,864.26
58	49	$267,478.50	$185,473.21
59	50	$267,478.50	$184,092.52
60	51	$267,478.50	$182,722.10
61	Total	$12,852,671.76	$10,604,259.89
PV_Calc

Cell Formulas
Range	Formula
E11:E59	E11	=IF($G$2>E10,E10+1,"")
E60	E60	=IF($G$2>=E59,E59+1,"")
G10:G60	G10	=IF(OR(E10="",F10=""),"",PV($G$6/12,E10,0,-F10,0))
F61	F61	=SUBTOTAL(109,[Cash])
G61	G61	=SUBTOTAL(109,[Present Value])

Cells with Data Validation
Cell	Allow	Criteria
G2	Whole number	between 1 and 1200

 

[Akuini]

I downloaded your file.
You give me an error version, formula in E12 is already messed up. So I manually enter the formula: =IF($G$8="Beginning",0,1)
I entered any number in G2, including 2 & 10, and the formula in E12 stayed the same, no error.

PV of lease pymts-Test.xlsm
	D	E	F	G
1
2			Number of Periods	10
3
4			Periods in	Months
5
6			Annual Rate	9,00%
7
8			Payment made at	End
9
10
11		Period	Cash	Present Value
12		1	$237.651,19	$235.882,07
13		2		$0,00
14		3		$0,00
15		4		$0,00
16		5		$0,00
17		6		$0,00
18		7		$0,00
19		8		$0,00
20		9		$0,00
21		10		$0,00
22
PV_Calc

Cell Formulas
Range	Formula
E12	E12	=IF($G$8="Beginning",0,1)
E13:E21	E13	=E12+1
G12:G21	G12	=PV(IF($G$4="Months",$G$6/12,IF($G$4="Quarters",$G$6/4,IF($G$4="bi-annual",$G$6/2,$G$6))),E12,0,-F12,IF($G$8="Beginning",0,1))

Cells with Data Validation
Cell	Allow	Criteria
G2	Whole number	between 1 and 1200
G8	List	=Sheet1!$A$1:$A$2
G4	List	=Sheet1!$A$4:$A$7

 

[Sean15]

okay, thanks.
Very much appreciated your help.

 

[Akuini]

You're welcome.