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.

named range row offset
 

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

wrote in message
ups.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.

named range row offset
 

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" wrote in message
...
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

wrote in message
ups.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.

named range row offset
 

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 !
---------------------------------------------------------------------------

wrote in message
ups.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.

named range row offset
 

Wow, that's perfect! Thanks so much.

named range row offset
 

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
ups.com...
Wow, that's perfect! Thanks so much.