Forum Discussion

Copper Contributor

Oct 10, 2020

Row dependent function

Want to form a function that is row dependent. For given columns E & G, the following functions appear in cells G7, G8 & G9 : G7=G6-E7 G8=G7-E8 G9=G8-E9 Form a general function of these equation...

MVP

Oct 10, 2020

How about

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+INDIRECT(ADDRESS(ROW(),COLUMN()-2))

Copper Contributor

Oct 10, 2020

HansVogelaar What goes inside the brackets? Does this formula need to be inserted into the relevant cell for each row? Not yet tried it out.

HansVogelaar
MVP
Oct 10, 2020
jomacapdb1

Nothing goes inside the brackets. ROW() returns the row number of the cell with the formula, and COLUMN() the column number of the cell with the formula.

Select the range into which you want the formulas, enter or copy/paste the formula from my previous reply and confirm it with Ctrl+Enter to enter it in all cells of the selection.
- jomacapdb1
  Copper Contributor
  Oct 11, 2020
  HansVogelaar Still require some more assistance before trying it out.
  1. How / where do you select the range for this formula? I suppose that could be the start cell and the end cell of the column for this formula.
  2. You say copy / paste the formula. But into which cell, or whereabouts on the spreadsheet?
  - HansVogelaar
    MVP
    Oct 11, 2020
    jomacapdb1
    
    Let's say that you want to apply the formula to G7:G100.
    
    Select this range G7:G100.
    
    Click in the formula bar.
    
    If there is already a formula, delete it.
    
    Type the formula from my reply manually, or copy it from that reply and paste it into the formula bar.
    
    Press Ctrl+Enter.

Resources