named range row offset

[barbetta3141]

I recently discovered naming entire rows -- it makes references super
easy (when your columns line-up). For example, if I name row 2
"MyRow", then in other cells I could just put "=MyRow" and it will
reference the cell in MyRow (row 2) that is in the same column where I
put the formula.

For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it
returns D2, E6 --> E2, you get the idea.

My question is whether there is an easy way to offset this by one
column (so that putting "=MyRow" in cell C6 returns B2 instead of C2).
Something like "=MyRow[-1]" would be great. Any ideas? Thanks.

 

[Biff]

Hi!

Try this:

=INDEX(MyRow,COLUMN()-1)

If you put this formula in column A it obviously can't refer to a column to
the left of column A so the value in column A will be returned.

Biff

 

[Biff]

P.S.

Also, this will not be able to reference column IV (256). You could do that
but then your simple reference method will turn out to be not so simple
afterall!

Biff

Hi!

Try this:

=INDEX(MyRow,COLUMN()-1)

If you put this formula in column A it obviously can't refer to a column
to the left of column A so the value in column A will be returned.

Biff

I recently discovered naming entire rows -- it makes references super
easy (when your columns line-up). For example, if I name row 2
"MyRow", then in other cells I could just put "=MyRow" and it will
reference the cell in MyRow (row 2) that is in the same column where I
put the formula.

For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it
returns D2, E6 --> E2, you get the idea.

My question is whether there is an easy way to offset this by one
column (so that putting "=MyRow" in cell C6 returns B2 instead of C2).
Something like "=MyRow[-1]" would be great. Any ideas? Thanks.

 

[Ragdyer]

Why not use 'Named Formulas' instead?

Try this:
Click in *any* cell except in Column A.
For demo purposes, say E4.

Now, <Insert> <Name> <Define>
In the "Names In Workbook" box, type in
MyRow

Then, in the "Refers To" box, enter
=D$2
Then <OK>

NOW ... enter 100 in say G2.
*Anywhere* in Column H enter
=MyRow

Is that good enough for you?

A named formula with relative column reference (relative to the cell in
focus during *creation*) and absolute row reference might be what you
want/need.

 

[barbetta3141]

Wow, that's perfect! Thanks so much.

 

[Ragdyer]

Appreciate the feed-back.