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-E7G8=G7-E8G9=G8-E9Form a general function of these equations so that there is no need to individually type in each equation for each cell.

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

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.

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.

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?

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.

Forum Discussion

jomacapdb1

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 equations so that there is no need to individually type in each equation for each cell.

excel

12 Replies

HansVogelaar
MVP
Oct 10, 2020
jomacapdb1

How about

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+INDIRECT(ADDRESS(ROW(),COLUMN()-2))
- jomacapdb1
  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.