Make number change as it goes down

[borabora12]

A1: =OFFSET('1'!$A$1,'1'!$N$1-38,0)
A2: =OFFSET('1'!$A$1,'1'!$N$1-37,0)
A3: =OFFSET('1'!$A$1,'1'!$N$1-36,0)
A4: =OFFSET('1'!$A$1,'1'!$N$1-37,0)

As I drag the little sqaure box on the bottom right of the cell is there anyway for excel to automatically change the number from 38 to 37 to 36 to 35 etc etc.? Thanks in advance.

 

[mikerickson]

A better formula would be

=INDEX('1'!$A:$A, 38-$N$1-ROW(), 1)

its non-volatile and will adjust as dragged down.

 

[T. Valko]

A1: =OFFSET('1'!$A$1,'1'!$N$1-38,0)
A2: =OFFSET('1'!$A$1,'1'!$N$1-37,0)
A3: =OFFSET('1'!$A$1,'1'!$N$1-36,0)
A4: =OFFSET('1'!$A$1,'1'!$N$1-37,0)

As I drag the little sqaure box on the bottom right of the cell is there anyway for excel to automatically change the number from 38 to 37 to 36 to 35 etc etc.? Thanks in advance.

What's in cell N1?

 

[T. Valko]

A better formula would be

=INDEX('1'!$A:$A, 38-$N$1-ROW(), 1)

its non-volatile and will adjust as dragged down.

Maybe, but that formula is dependent upon which row it's entered on. Insert a new row before the formula cell and the formula breaks.

 

[borabora12]

Thank you so much!

I got it to work by tweaking it a little bit but you definitely pointed me in the right direction by introducing me to indexing. Thanks!

 

[mikerickson]

=index('1'!$a:$a, 38-$n$1-row(a1), 1)

 

[Aladin Akyurek]

=index('1'!$a:$a, 38-$n$1-row(a1), 1)

In order to robustify the formula against row insertions...

ROW()-ROW($A$1)+1

or bettter:

ROWS($A$1:A1)

 

[T. Valko]

In order to robustify the formula against row insertions...

ROW()-ROW($A$1)+1

or bettter:

ROWS($A$1:A1)

There's no need to make the column absolute.

ROWS(A$1:A1)