Cell referencing?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 05 Sep 2007 13:01 GMT

There may not be a way to do this and I can't find any documentation on
it.....

I want to vary the row number in a cell reference. For example, in one
sheet of a workbook I refer to a cell in another sheet in the same workbook.

=sheet2!M8+P7 where M8 is on sheet 2 and P7 is a cell in the current
sheet.

I need to increment the 8 by 2 such that the formula in the next cell to the
right on the current sheet will be...
=sheet2!M10+P7
and so on for many cells to the right.

Reply to this Message

Don Guillett - 05 Sep 2007 13:09 GMT

have a look in the help index for MOD

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> There may not be a way to do this and I can't find any documentation on
> it.....
[quoted text clipped - 10 lines]
> =sheet2!M10+P7
> and so on for many cells to the right.

Reply to this Message

Randy - 05 Sep 2007 13:37 GMT

I did, all I find is very simplistic instructions on how to reference cells,
I know how to do that. I need a way to vary the 8, I have tried stuff
like....
=sheet2!M[8+2]
=sheet2!M(8+2)
=sheet2!M'R6' (where R6 would equal 10)

I thought perhaps that using R1C1 style of referencing may work, but there
are other sheets in the workbook that reference cells in sheet2 and do not
use that type of referencing.
Randy

> have a look in the help index for MOD
>
[quoted text clipped - 12 lines]
>> =sheet2!M10+P7
>> and so on for many cells to the right.

Reply to this Message

Don Guillett - 05 Sep 2007 14:31 GMT

see if this helps
=ROW(A1)*8+$H$10

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I did, all I find is very simplistic instructions on how to reference
>cells, I know how to do that. I need a way to vary the 8, I have tried
[quoted text clipped - 24 lines]
>>> =sheet2!M10+P7
>>> and so on for many cells to the right.

Reply to this Message

Max - 05 Sep 2007 14:59 GMT

>> =sheet2!M8+P where M8 is on sheet 2 and P7 is a cell in the current
>> sheet.
>> I need to increment the 8 by 2 such that the formula in the next cell to
>> the right on the current sheet will be...
>> =sheet2!M10+P7

Try this in the starting cell in the current sheet:
=OFFSET(Sheet2!$M$8,COLUMNS($A:A)*2-2,)+$P7

Above returns : =sheet2!M8+P7

When you copy it across,
it'll return: =sheet2!M10+P7,
then: =sheet2!M12+P7,
and so on .. , as required

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Andy - 05 Sep 2007 15:03 GMT

> There may not be a way to do this and I can't find any documentation on
> it.....
[quoted text clipped - 10 lines]
> =sheet2!M10+P7
> and so on for many cells to the right.

Look at the OFFSET function.
The format is simply:-
Offset ('base cell'- in your case M8, number of rows to offset, number of
columns to offset,Option number of rows*, option number of columns*)

* the optional height and width applies to a range, if you want a single
cell then put the closing bracket after number of columns to offset)

your examples above would become:-
=offset(sheet2!M8,x,0)+P7 where x is 0
=offset(sheet2!M8,x,0)+P7 where x is 2 and so on.

Andy

Reply to this Message

Harlan Grove - 06 Sep 2007 07:11 GMT

"Randy" <pete@hotmail.com> wrote...
...

>I want to vary the row number in a cell reference. For example, in one
>sheet of a workbook I refer to a cell in another sheet in the same
>workbook.
>
>=sheet2!M8+P7

...

>I need to increment the 8 by 2 such that the formula in the next cell to
>the right on the current sheet will be...
>
>=sheet2!M10+P7
>
>and so on for many cells to the right.

So they all add the same P7?

If you were entering this formula in cell X99, try

X99:
=INDEX(Sheet2!$M:$M,6+2*COLUMNS($X99:X99))+$P7

Fill X99 right as far as needed.

Reply to this Message