References that move down as formula moves across - and vice versa?

[Michelle]

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

 

[Jacob Skaria]

Do you mean the below demonstration..

Col A Col B Col C Col D Col E
1 = = = =
2
3
4
5
6
7

Try the formula in place of = in cell B1 and copy to right as required which
will copy the values from A1,A2, A3 etc;

=INDIRECT("A" & COLUMN(B1)-1)

If this post helps click Yes

 

[Luke M]

Can use INDIRECT

=INDIRECT("R"&COLUMN(A1)&"C"&ROW(A1),FALSE)

To explain:
This formula is creating a cell reference using R1C1 style referencing (the
false at end).

By referencing COLUMN in the R spot, you cause formula to move vertically
when you copy horizontally. Conversely, the ROW function in the C spot causes
formula to move horizontally when you copy vertically.

The formula I gave then will work in both directions (although I admit, I
would find this very confusing to work with on a large scale)

 

[RagDyer]

Probably the easiest to explain to novices might be the Index() function.

The range you're copying *from* is the range you enter first.

Column:
=Index($A1:$A100
=Index($K20:$K50

Row:
=Index(A$1:Z$1
=index(J$41:AM$41

The second part of the formula will *automatically* increment as it's copied
*across*:
=Index($A1:$A100,Columns($A:A))
=index($K20:$K50,Columns($A:A))

OR, for copying *down*:

=Index(A$1:Z$1,Rows($1:1))
=Index(J$41:AM$41,Rows($1:1))

You see here, that the second part *never* changes, dependent on copying
either across or down.

Also, these formulas can be entered *anywhere* on the sheet, without having
to be changed in any way.