Formula for incremental cell reference

[Craig Schiller]

Hi all -

I'm sure this is easy but I haven't been able to find it in Excel's help
file.

Using Excel 2000.

In cell S11, I want to show the contents of cell A310.
In cell S12, I want to show the contents of cell A317.
In cell S13, I want to show the contents of cell A324.
etc.

The increment in the target row number is always seven.

What do I need to put in S11 and S12 so that I can merely copy down as
far as I want?

Thanks in advance.

Craig

 

[Peo Sjoblom]

Here you are


=INDEX($A$310:$A$65534,ROWS($A$1:A7)*7-48)

put that in S11 and copy it down

--


Regards,


Peo Sjoblom

 

[AltaEgo]

Put this formula at S11 and use the fill handle:

=OFFSET($A$310,(ROW()-11)*7,0)

 

[ShaneDevenshire]

Hi,

You can also use something like this:

=INDIRECT("C"&ROW(A1)*7+310)

 

[ShaneDevenshire]

I should also add that you can use INDEX and OFFSET as follows:

OFFSET(A$310,ROW(A1)*7,)
and
=INDEX(C:C,ROW(A1)*7+310)

 

[Craig Schiller]

Hey, Peo, that worked great! Thanks so much.

If it wouldn't be too much trouble, could you explain WHY it worked? I
can't parse it myself.

Thanks again.

Craig

 

[Craig Schiller]

Hi Shane -

Thanks for the response. But I don't understand what the reference to
"C" is?

Craig

***

ShaneDevenshire wrote:
I should also add that you can use INDEX and OFFSET as follows:

OFFSET(A$310,ROW(A1)*7,)
and
=INDEX(C:C,ROW(A1)*7+310)

 

[Craig Schiller]

Hi Shane -

I don't understand the reference to "C" here either. Could you explain?

Craig

***



ShaneDevenshire wrote:
Hi,

You can also use something like this:

=INDIRECT("C"&ROW(A1)*7+310)

 

[Craig Schiller]

Thanks Steve

That works. And I can kind of understand it, which is a bonus.

How would I modify it to work so that:

In cell T11, I show the contents of cell L310
In cell T12, I show the contents L317
etc.

TIA,
Craig

****************************

Steve wrote:

Put this formula at S11 and use the fill handle:

=OFFSET($A$310,(ROW()-11)*7,0)

 

[Peo Sjoblom]

The index part just gives you the range to work with and the rest of index
is number of rows followed by number of columns and since we are only
looking at 1x1 eg one column we don't have to put anything in the column
part.
So we have the range

$A$310:$A$65534

which needs to be absolute or else it will change when we copy it down, then
we want every 7th row starting
with A310 so we want

A310
A317
A324
and so on

meaning the row index should be

1
8
15
and so on

if you put


INDEX($A$310:$A$65534,1)


it will return what's in A310


INDEX($A$310:$A$65534,8)


will return what's in A317


this part


=ROWS($A$1:A7)*7-48

will return

1
8
15
and so on when copied down

How?

well ROWS(A1:A7)

simply counts the rows between A1:A7 thus 7

since we want to copy it down and incrementing we need to anchor
it by using the dollar sign for A1

$A$1 or else that formula will return

ROWS(A2:A8)

copied down one row which will still be 7

now how do we get 7, 8, 9 etc to turn into

1, 8, 15


=ROWS($A$1:A7)*7

If we multiply 7 by 7 we'll get 49, then

8 by 7 (next row since the ROWS part will return 8 then multiplied by 7))
we'll get 56 and so on

So how do we change that into 1, 8, 15?

If we subtract 48 from 49 we'll get 1
then the next row we'll subtract 48 from 56 which is 8
then 63-48 which is 15


The formula one might think of first is probably OFFSET where you anchor the
first cell (A310)
however it is a volatile formula and it will recalculate every time there is
a calculation meaning if the workbook
is big it will slow down the workbook plus it gives you strange things like
prompting you to save
the workbook when you just opened without doing any changes. Same goes for
INDIRECT







--


Regards,


Peo Sjoblom

 

[Craig Schiller]

Thanks so much for your very clear explanation. This is a wonderfully
helpful newsgroup. Thanks to you and everyone.

Craig

 

[Peo Sjoblom]

Thanks for the feedback Craig

--


Regards,


Peo Sjoblom

 

[AltaEgo]

1) Paste the formula shown below
2) Change the 'A' in the formula to 'L'

 

[AltaEgo]

Shane possibly has the skills to churn these out in his sleep but possibly
responded without proof-testing. I am working out of years of using a
narrower range of functions and currently cannot afford NOT to proof test
:-{

To amend Shanes formula, paste the following at S12 before dragging

=INDEX(A:A,(ROW(A1))*7+310)

NOTE that you need to put "=310" at S11 because the above will not drag up
without error due to Row(A1) referencing the top row of the spreadsheet.

For your second question, just change the A:A reference to the appropriate
column.