Autofill

  • Thread starter Thread starter Thomas Richardson
  • Start date Start date
T

Thomas Richardson

Can anyone help me solve a problem with Autofill, please?

I need to import the content of every seventh cell in a column from another
worksheet (in fact a weekly sum), and I'd like to do that using autofill. I
don't know how to set up a formula in the second worksheet that will enable
me to drag down and so import every seventh cell content, whilst ignoring
the six cells in between.

In other words, I'd like to import the contents of A7 then A14 then A21 etc
from Worksheet1 to A1, A2, A3 etc in Worksheet2 by inputting a formula into
Worksheet2(A1) then dragging down. Can this be done?
 
On Sheet2, enter the following formula in A1 and drag down:

=OFFSET(Sheet1!$A$1,((ROW(1:1)-ROW($A$1)+1)*7)-1,0)

Hope this helps!
 
Forgot to add, that my original formula will work only when entered
(started) in Row1 of the "Summary" sheet.

This will work while being entered into *any* row:

=INDIRECT("Sheet1!A"&ROW(A1)*7)
 
Ragdyer said:
Little shorter, plus easier on XL's processing:

=INDIRECT("Sheet1!A"&ROW()*7)
Click to expand...

Yep! Definitely much better, especially with the subsequent change you
made to the formula for robustness. :-)
 
Back
Top