SOLVED

is is possible to update a cell formula in a column

Occasional Contributor

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

15 Replies

@lbeamer 

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).

@lbeamer 

Yes, 10 ... 19 is 10 rows in total, but if you want the formula to change in row 10, row 19, row 28, row 37 etc., that is every 9 rows, not every 10 rows.

@lbeamer 

It would also mean that the conditional formula I gave you earlier is incorrect...

Again, thank you so much for assisting me on this very difficult Column H update. In my inquiry, the Column I row was 1 row prior to the Column H row. Notice row 10 stated $I$row9.
I did not receive your suggestion on how a Conditional formula may solve this.
Example: Column H

Row10 = Column D - $I$row9

Row 11 = Column D - $I$row9

..........

Row 19 =Column D - $I$row18

@lbeamer 

Row 10 has D10-I9

...

Row 18 has D18-I9

Row 19 has D19-I18

The jump is 9 rows, not 10 rows.

@lbeamer

                                                                      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.

 

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.

@lbeamer 

I'm afraid that doesn't help at all; it is very difficult to read. Please attach a sample workbook.

How do I attach a worksheet?

@lbeamer 

Drag the workbook from File Explorer into the area below the box where you enter your reply. It says "Drag and drop here or browse files to attach".

@lbeamer 

Thanks. I'm still confused.

Why does H2 have =D2-$I$2, while H12 has =D12-$I$11 and H22 has =D22-$I$21 ?

Shouldn't H2 have =D2-$I$1 ?

(And why does H1 have =D1-$I$7218 ?)

@lbeamer 

It's past midnight where I live, I'm off to bed now.

The formula in H12 could be

=D12-INDEX(I:I,10*QUOTIENT(ROW(H12)-2,10)+1)

This can be filled down.

For H1:H11 it'll return different results than your formula.

best response confirmed by lbeamer (Occasional Contributor)
Solution
Thank you so much> I will try this and you get a great night of rest!