named range row offset

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.

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

<barbetta3141@yahoo.com> wrote in message
news:1152220714.294010.113130@75g2000cwc.googlegroups.com...
>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.
>

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

"Biff" <biffinpitt@comcast.net> wrote in message
news:uHjPzUUoGHA.680@TK2MSFTNGP03.phx.gbl...
> 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
>
> <barbetta3141@yahoo.com> wrote in message
> news:1152220714.294010.113130@75g2000cwc.googlegroups.com...
>>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.
>>
>
>

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<barbetta3141@yahoo.com> wrote in message
news:1152220714.294010.113130@75g2000cwc.googlegroups.com...
>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.
>

Wow, that's perfect! Thanks so much.

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
<barbetta3141@yahoo.com> wrote in message
news:1152282411.094800.311540@75g2000cwc.googlegroups.com...
> Wow, that's perfect! Thanks so much.
>