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 <486D107D-430E-4387-A7ED-90E5E0587273@microsoft.com>,
"Patti" <Patti@discussions.microsoft.com> 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 <486D107D-430E-4387-A7ED-90E5E0587273@microsoft.com>,
> "Patti" <Patti@discussions.microsoft.com> 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?
>