Forum Discussion
is is possible to update a cell formula in a column
I have a 7000 row spreadsheet that I have to manually update every 10 rows. It is a simple update in column H that requires a new value from column I every 10 rows.
Example: Column H
Row10 = Column D - $I$row9
Row 11 = Column D - $I$row9
..........
Row 19 =Column D - $I$row18
- Thank you so much> I will try this and you get a great night of rest!
15 Replies
D E F G H I
8/17/2021
444.23999
444.959991
440.850006
444.04
444.040009
92673900
10.85
432
8/18/2021
442.959991
444.630005
438.920013
439.18
439.179993
89351900
8.92
431
8/19/2021
436.269989
441.140015
436.119995
439.86
439.859985
92812200
6.12
432
8/20/2021
440.230011
443.709991
439.709991
443.36
443.359985
71975900
9.71
432
8/23/2021
445.160004
448.230011
443.440002
447.26
447.26001
54973000
13.44
431
8/24/2021
447.970001
448.540009
447.420013
447.97
447.970001
38744700
17.42
431
8/25/2021
448.170013
449.459991
447.769989
448.91
448.910004
40529700
17.77
431
8/26/2021
448.609985
448.859985
446.160004
446.26
446.26001
57829600
16.16
431
8/27/2021
447.119995
450.649994
447.059998
450.25
450.25
77201900
17.06
431
8/30/2021
450.970001
453.070007
450.709991
452.23
452.230011
48357400
19.71
429
8/31/2021
452.130005
452.48999
450.920013
451.56
451.559998
59300200
19.92
429
9/1/2021
452.559998
453.109985
451.549988
451.8
451.799988
48721400
20.55
429
9/2/2021
453.320007
454.049988
451.910004
453.19
453.190002
42501000
20.91
432
9/3/2021
451.980011
453.630005
451.549988
453.08
453.079987
47170500
20.55
436
9/7/2021
452.709991
452.809998
450.73999
451.46
451.459991
51671500
19.74
439
9/8/2021
450.890015
451.670013
448.859985
450.91
450.910004
56121700
17.86
440
9/9/2021
450.700012
452.570007
448.720001
448.98
448.980011
57970400
17.72
440
9/10/2021
451.040009
451.48999
445.309998
445.44
445.440002
89848000
14.31
438
9/13/2021
448.640015
448.920013
444.109985
446.58
446.579987
83738600
6.11
439
9/14/2021
448.119995
448.339996
443.220001
444.17
444.170013
78197100
5.22
436
9/15/2021
444.619995
448.410004
443.440002
447.88
447.880005
78792200
5.44
436
9/16/2021
447.320007
448.359985
444.019989
447.17
447.170013
77695700
6.02
435
09/17/21
444.92
445.37
441.02
441.4
441.4
118229200
3.02
433
9/20/2021
434.880005
436.559998
428.859985
434.04
434.040009
166445500
-9.14
426
9/21/2021
436.529999
437.910004
433.070007
433.63
433.630005
92526100
-4.93
422
9/22/2021
436.049988
440.029999
433.75
437.86
437.859985
102350100
-4.25
421
9/23/2021
439.850006
444.890015
439.600006
443.18
443.179993
76396000
1.60
422
9/24/2021
441.440002
444.670013
441.209991
443.91
443.910004
62025800
3.21
423
9/27/2021
442.809998
444.049988
441.899994
442.64
442.640015
61371100
18.90
423
9/28/2021
439.690002
440.040009
432.940002
433.72
433.720001
130436300
9.94
421
9/29/2021
435.190002
437.040009
433.850006
434.45
434.450012
82329200
10.85
419
9/30/2021
436.019989
436.769989
428.779999
429.14
429.140015
140181200
5.78
417
10/1/2021
430.980011
436.029999
427.230011
434.24
434.23999
129240100
4.23
418
10/4/2021
433
433.959991
426.359985
428.64
428.640015
128570000
3.36
416
10/5/2021
430.23999
435.48999
429.390015
433.1
433.100006
90682500
6.39
415
10/6/2021
429.269989
435.119995
427.540009
434.9
434.899994
113032200
4.54
416
10/7/2021
438.390015
441.679993
438.200012
438.66
438.660004
72437500
15.20
416
10/8/2021
439.480011
439.890015
437.190002
437.86
437.859985
74492900
14.19
417
10/11/2021
437.160004
440.26001
434.619995
434.69
434.690002
65233300
17.62
418
So every 10 rows, Column H uses a new Column I for the next 10 rows. So date 8/30 uses the I value from 8/27 of 431 in column H for 8/30 thru 9/10. Then on 9/11 the next 10 days use 444 in the Column H formula so 9/13 Column D is 444.1099 and column H uses 444.1099 - 438 for the resultant 6.11.then 9/14 Column H uses Column D 443.22 -438 for resultant 5.22 and so on until 9/27 when Column I becomes 423. I hope this clarifies my dilemma.
You write "every 10 rows", but from row 10 to row 19 is only 9 rows...
- thank you so much for your reply
10
11
12
13
14
15
16
17
18
19
I count 10 rows, but you have been very helpful in making my spreadsheet format looking better. this one question is harder in my opinion may not be possible to automatically update Column H formula with a new $I$row (+10).It would also mean that the conditional formula I gave you earlier is incorrect...
