incrementing formula reference by 7
 

I've tried several different ways to do this with no luck. I have a formula
in a cell that refers to a cell on another worksheet in the same book. I need
to have each subsequent formula (horizontally) refer to the cell referenced,
but offset by 7 rows.

Here's the example:
Cell A1 formula Cell A2 should refer to Cell A3
should refer to
='Raw Data'!$E1 ='Raw Data'!$E8 ='Raw Data'!$E15

Help please?

one way:

=INDEX('Raw Data'!$E:$E,(ROW()-1)*7+1)

In article ,
"Patti" wrote:

I've tried several different ways to do this with no luck. I have a formula
in a cell that refers to a cell on another worksheet in the same book. I need
to have each subsequent formula (horizontally) refer to the cell referenced,
but offset by 7 rows.

Here's the example:
Cell A1 formula Cell A2 should refer to Cell A3
should refer to
='Raw Data'!$E1 ='Raw Data'!$E8 ='Raw Data'!$E15

Help please?

I must be totally dumb today because it didn't work.
This is what I have on the ws Raw Data in cells D1:E12. Value in E1 is 60
and Value in D8 is 69 (row 6 in a blank row).
1-Critical Impact 60
2-Major Impact 225
3-Moderate Impact 12552
4-Minor Impact 5236
5-No Impact 416

1-Critical Impact 69
2-Major Impact 224
3-Moderate Impact 12621
4-Minor Impact 5469
5-No Impact 316

On my other ws i entered your formula, but with column instead of row and
dragged it horizontally and that worked. So now i have all the critical
impact values on one row (for charting). I need to do the same for each
category, major impact, moderate, etc. The data i'm pulling from is all as
above, 5 lines of data, one blank line, then 5 more lines of data. I need to
do the same for each new line of data?
Can you help with that too?

thx in advance




"JE McGimpsey" wrote:

one way:

=INDEX('Raw Data'!$E:$E,(ROW()-1)*7+1)

In article ,
"Patti" wrote:

I've tried several different ways to do this with no luck. I have a formula
in a cell that refers to a cell on another worksheet in the same book. I need
to have each subsequent formula (horizontally) refer to the cell referenced,
but offset by 7 rows.

Here's the example:
Cell A1 formula Cell A2 should refer to Cell A3
should refer to
='Raw Data'!$E1 ='Raw Data'!$E8 ='Raw Data'!$E15

Help please?