Highlighted
Visitor

Autofill a formula series

Hi. I'm trying to use a formula to take values from every 3rd cell of a column in sheet1 and display them in a column in sheet2 but one after another.

So, column A of sheet 2 I want the following:

cell1 = "=sheet1!B3"

cell2 = "=sheet1!B6"

cell3 = "=sheet1!B9"

cell4 = "=sheet1!B12"    and so on.

When I try to use the 'fill series' option I get:

cell1 = "=sheet1!B3"

cell2 = "=sheet1!B4"

cell3 = "=sheet1!B5"

cell4 = "=sheet1!B6"

And even if I manually input the formulas into cells A1 and A2 and then highlight them both and try to fill series, I get:

cell1 = "=sheet1!B3"

cell2 = "=sheet1!B6"

cell3 = "=sheet1!B7"

cell4 = "=sheet1!B8"

Is there a way that I can autofill the formula to get what I'm after?

Apologies if I haven't explained this very well

4 Replies
Highlighted

Re: Autofill a formula series

In the absence of the worksheet itself, the one you're working with, let me just suggest you investigate the INDIRECT function. I think it will work to solve your need. Here's some of the Help text on it.

Highlighted

Re: Autofill a formula series

@tonekil83Hey there, I think I can help you. Use the Offset Formula. In Sheet1 Cell C2 (or blank adjacent Cell) input a 0 then drag it all the way down to the end of your data, set to fill Series so the numbers truncate. (from 0-100 or whatever the end number might be). Sheet2 do the same thing. In Sheet2 Cell B enter this formula (Example will use cell B2 in Sheet2 to bring the data into this sheet). Adjust formula as needed. =IF(OFFSET(Sheet1!\$B\$2,Sheet2!C2,0)=0,"",OFFSET(Sheet1!\$B\$2,Sheet2!C2,0))

Highlighted

Re: Autofill a formula series

@tonekil83I forgot something, or actually I told you wrong, in Sheet 2 Cell C2 enter 0 then Cell C3 enter 2. Then, Highlight those cells and drag them down to fill the series. It will show as:

C2 = 0

C3 = 2

C4 = 4

C5 = 6

Highlighted

Re: Autofill a formula series

If that's Sheet2

for the Sheet1

in A5 enter

``=INDEX(Sheet2!B:B,3*(ROW()-ROW(\$A\$4)))``

and drag it down.