How can I refer to a row

[Mark F]

I want to refer to a fixed row in the spreadsheet, but to the column
that matches the column that the reference is in, but I want to
use a name for the fixed row.

for example, Row 2 has the multipliers that I need in the formula, so
I now have in cell F10
+F$2*{etc}

For clarity, and perhaps for moving things around, instead of using $2
as part of the cell reference, I'd like to be able to use a
"Row label".
and have something similar to:
+F${Row label}{etc}
in cell F10

Does this concept exist?

I tried Insert > Name > Label
but it seemed like I could only name a part of a row or a column, not
define something to be a row number.

I thought about:
1. insert a column,
2. label the cell in that column and that row as
MultiplierRowNumber,
3. put +ROW() in the cell MultiplierRowNumber.

I then might be able to get the value that I am interested in by using
the ADDRESS function, MultiplierRowNumber, and the number of the
current cell, but that seemed like it was too complicated to be the
right way to do things.

 

[trip_to_tokyo]

I want to refer to a fixed row in the spreadsheet, but to the column
that matches the column that the reference is in, but I want to
use a name for the fixed row.

for example, Row 2 has the multipliers that I need in the formula, so
I now have in cell F10
    +F$2*{etc}

For clarity, and perhaps for moving things around, instead of using $2
as part of the cell reference, I'd like to be able to use a
"Row label".
and have something similar to:
    +F${Row label}{etc}
in cell F10

Does this concept exist?

I tried Insert > Name > Label
but it seemed like I could only name a part of a row or a column, not
define something to be a row number.

I thought about:
1. insert a column,
2. label the cell in that column and that row as
     MultiplierRowNumber,
3. put +ROW() in the cell MultiplierRowNumber.

I then might be able to get the value that I am interested in by using
the ADDRESS function, MultiplierRowNumber, and the number of the
current cell, but that seemed like it was too complicated to be the
right way to do things.

EXCEL 2007
Click, for example, the number 2 in row 2 so that the entire row is
highlighted.

Formulas tab / Defined Names group / click on Name Manager.

The Name Manager should open.

Click:-

New . . .

- in top left hand corner.

Enter, say:-

row_2

- in the:-

Name:

- field

Click OK then Close.

You can now refer to row 2 as:-

row_2

Hope the above helps.

 

[Mark F]

EXCEL 2007
Click, for example, the number 2 in row 2 so that the entire row is
highlighted.

Formulas tab / Defined Names group / click on Name Manager.

The Name Manager should open.

Click:-

New . . .

- in top left hand corner.

Enter, say:-

row_2

- in the:-

Name:

- field

Click OK then Close.

You can now refer to row 2 as:-

row_2

Hope the above helps.

The syntax was a little different in Excel 2003 (I selected the row
then did Insert > Name > Define...)

However I think I need something more.

I defined things so SpecialRowA Refers to: =Sheet1!$1:$1.
When I moved the row to row 9 the definition (correctly) changed to
Refers to: =Sheet1!$9:$9

However I now find that I don't know the syntax put an entry in a cell
and refer to the corresponding cell in the named row.

I'd like to put something like this in a cell:
+{SpecialRowA,this column}

If I didn't want to be able to use a name to refer to the a special
row that I might want to move, I would:
1. select the cell to make the reference in
2. type "="
3. select the corresponding cell in the special row.
This would put the cell in the special row in the formula,
so it might read:
=E3
4. I would now change the name to E$3. I could copy the cell
elsewhere on the sheet and the copy operation would automatically
change the "E" to correspond to each cell's column and leave the
"$3" part fixed.

However, I want the formulas in all the cells so use SpecialRowA
instead of 3, so that I can move the special row elsewhere.