1

I need help on using a formula so that when I use autofill and drag the cell in will link the formula vertically/horizontally like in the picture.

I don't wanna use transpose since i want the data link to the other table everytime I did some changes.

enter image description here

1 Answer 1

1

Something like this formula

=OFFSET($A$3,,ROW()-ROW($A$3))

in A4:A9?

enter image description here

Edit

In this formula, ROW($A$3) is the row number of the cell A3, that is, just a number 3. Dollar prefixes in front of A and 3 means that this is an absolute reference, and it will stay the same when copied to another cell(s). Just ROW() means the row number of the cell, where the formula is, so it would mean 4 for the cell A4, 5 for the cell A5 and so on. The combination ROW()-ROW($A$3) is a standard way to have a consequent numbers in a column of cells, so in the example, for the cell A4, expression

ROW()-ROW($A$3)

means

4 - 3 = 1,

for A5 - =2, and so on.

The whole formula

=OFFSET($A$3,,ROW()-ROW($A$3))

for every cell can be translated as follows: add ROW()-ROW($A$3) numbers of columns to the column number, where $A$3 is (which is 1, since A is the first column), and copy the content of the cell found to the cell, where the formula is.

For more details and examples on functions OFFSET and ROW, check out the Excel help section Lookup & Reference functions.

3
  • yes it works, thanks a lot. btw is these a two formula and if you can explain how offset and row works? Commented May 21, 2017 at 23:45
  • @Jeffrey JR Villegas: Not sure what do you mean by "a two formula", the formula is the same for all the cells in selection (A4:A9 in the example).
    – g.kov
    Commented May 22, 2017 at 0:07
  • because i saw a "row" in the index formula, sorry if i confused you,,,im still learning how index formula works and how to use it in different situations. thanks Commented May 22, 2017 at 0:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .