Cell referencing?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

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.
 
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
 
see if this helps
=ROW(A1)*8+$H$10

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Randy said:
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

Don Guillett said:
have a look in the help index for MOD

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
Click to expand...
 
=sheet2!M8+P where M8 is on sheet 2 and P7 is a cell in the current
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

---
 
Randy said:
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.
Click to expand...

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
 
Randy said:
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.
Click to expand...

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.
 
Back
Top