How can I 'fill' a range of cells incrementing the cell reference within a formula

Hi,
I want to copy a formula in a cell to the cells beneath it by incrementing the cell reference by 6.
For example, here is the formula...
=INDIRECT($D$6&"!"&"B11")
I want to copy down so the next cell adds 6 to the cell reference thus....
=INDIRECT($D$6&"!"&"B17")
...and so on.
I tried using Autofill but it didn't work.
Is there any way of doing this other than manually changing each formula or using VBA ?
Thanks
Toco
TocogroupAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
ALong the lines of:

=INDIRECT($D$6&"!" & ADDRESS((ROW()-10)*6+5,2))

I suspect

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Assuming you are starting with the subject formula in row 1 then one example would be:

=INDIRECT($D$6&"!" & ADDRESS(ROW()*6+5,2))

Chris
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
You might use  OFFSET to achieve this.
as reference you have the previous found cell, then offset would calculate the increment of 6...

Kr

Eric
0
 
TocogroupAuthor Commented:
Chris,
What would the formula be if I was starting with the subject formula in row 11 of the other sheet ?
Your example returned me a zero.
0
 
TocogroupAuthor Commented:
Thanks for that Chris. It works a treat. I wasn't too sure how ADDRESS and ROW were working together but I've got it now.
many thanks
Toco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.