How to fill an ongoing sequence of formulas?

[Allan]

I am trying to fill an ongoing sequence of formulas.

I can fill a sequence of values, but when I fill formulas it doesn't
fill with the ongoing series, it just copies the orignal formulas so I
end up with a repeat of the same values. I am trying to extract only
the values that occur on sheet1 every 15 rows down a column. It's a
large database so it is not feasable to paste links manually.

The sequence I want to fill is:
=Sheet1!$F$2
=Sheet1!$F$17
=Sheet1!$F$32
and so on adding 15 to the row reference.

Any help would be greatly appreciated.

Allan

 

[Simon Livings]

You might want to investigate using the INDIRECT function to do this,
unless you want to use VBA to generate the formulas for you. The
INDIRECT function you could use (assuming you are starting in A1) could
be:

= INDIRECT ("Sheet1!$F$" & (row(A1)-1)*15 + 2)

this will essentially create the references jumping up by 15 each time
using the row numbers as the multiplication factor each time. The
formula can then be copied down the sheet as required.

There are pitfalls with using INDIRECT so it is probably worth reading
the help text before using this for definite.

 

[Simon Livings]

You might want to investigate using the INDIRECT function to do this,
unless you want to use VBA to generate the formulas for you. The
INDIRECT function you could use (assuming you are starting in A1) could
be:

= INDIRECT ("Sheet1!$F$" & (row(A1)-1)*15 + 2)

this will essentially create the references jumping up by 15 each time
using the row numbers as the multiplication factor each time. The
formula can then be copied down the sheet as required.

There are pitfalls with using INDIRECT so it is probably worth reading
the help text before using this for definite.

 

[Allan]

You might want to investigate using the INDIRECT function to do this,
unless you want to use VBA to generate the formulas for you. The
INDIRECT function you could use (assuming you are starting in A1) could
be:

= INDIRECT ("Sheet1!$F$" & (row(A1)-1)*15 + 2)

this will essentially create the references jumping up by 15 each time
using the row numbers as the multiplication factor each time. The
formula can then be copied down the sheet as required.

There are pitfalls with using INDIRECT so it is probably worth reading
the help text before using this for definite.

Hi Simon,

Thanks, this is brilliant!

After a bit of trial and error I managed to get it working to target
specific cells in sheet1. My worksheet has 15 rows which then repeat
with the next lot of data, so for example to capture data where the
first value was in sheet1 B7, I pasted the following link in sheet2 H3,
= Sheet1!$B$7

in H4 I used the following formula

=INDIRECT("Sheet1!$B$"&(ROW(H7)-6)*15+7)

I could then fill this formula down the row and it correctly inputed
data from sheet1 B22, B37 etc.

I found I can fill down to row 4370, the cells from row 4371 onwards
have a #REF! error for some reason.
I guess there is some limitation preventing further filling down the
same worksheet. However what I've got so far is a big help.

Thanks again

Regards

Allan