How to select cells (located in a column) for formulas in a row automatically?

D

Dmitry Kopnichev

Hello
How to use cells (located in a column) in formulas in a row automatically? I
cannot copy the formula to other cells in the row because the coping moves
the selection to another column, not to a another row in the same column.
 
D

Dorian C. Chalom

Use a $ (dollar sign) infront of the row or column you do not wat to
change.
 
D

Dmitry Kopnichev

The $ (dollar sign) will not move the selection in the column accordingly;
will keep the selection the same. I want to use other cells in the column in
other formulas in the row.
 
H

Herbert Seidenberg

Use names.
Assuming your data looks like this:
Data Results
1 1
2
3 1
4 2
5 3
6 4
7 5
8
9 1
10 2
Select all and
Insert > Name > Create > Top Row
The formula for the first cell in Results is
=INDEX(Data,1)
and for the second block in Results
=INDEX(Data,1):INDEX(Data,5)
and an alternate formula for the last block
=OFFSET(Data,0,0,2)
The last two are array formulas entered with
Shift+Ctrl+Enter.
You can copy the Results to any row or column
without changes.
 
Top