Autofill a formula series

%3CLINGO-SUB%20id%3D%22lingo-sub-1288579%22%20slang%3D%22en-US%22%3EAutofill%20a%20formula%20series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288579%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I'm%20trying%20to%20use%20a%20formula%20to%20take%20values%20from%20every%203rd%20cell%20of%20a%20column%20in%20sheet1%20and%20display%20them%20in%20a%20column%20in%20sheet2%20but%20one%20after%20another.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20column%20A%20of%20sheet%202%20I%20want%20the%20following%3A%3C%2FP%3E%3CP%3Ecell1%20%3D%20%22%3Dsheet1!B3%22%3C%2FP%3E%3CP%3Ecell2%20%3D%26nbsp%3B%22%3Dsheet1!B6%22%3C%2FP%3E%3CP%3Ecell3%20%3D%26nbsp%3B%22%3Dsheet1!B9%22%3C%2FP%3E%3CP%3Ecell4%20%3D%26nbsp%3B%22%3Dsheet1!B12%22%26nbsp%3B%20%26nbsp%3B%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20use%20the%20'fill%20series'%20option%20I%20get%3A%3C%2FP%3E%3CP%3Ecell1%20%3D%20%22%3Dsheet1!B3%22%3C%2FP%3E%3CP%3Ecell2%20%3D%26nbsp%3B%22%3Dsheet1!B4%22%3C%2FP%3E%3CP%3Ecell3%20%3D%26nbsp%3B%22%3Dsheet1!B5%22%3C%2FP%3E%3CP%3Ecell4%20%3D%26nbsp%3B%22%3Dsheet1!B6%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20even%20if%20I%20manually%20input%20the%20formulas%20into%20cells%20A1%20and%20A2%20and%20then%20highlight%20them%20both%20and%20try%20to%20fill%20series%2C%20I%20get%3A%3C%2FP%3E%3CP%3Ecell1%20%3D%20%22%3Dsheet1!B3%22%3C%2FP%3E%3CP%3Ecell2%20%3D%26nbsp%3B%22%3Dsheet1!B6%22%3C%2FP%3E%3CP%3Ecell3%20%3D%26nbsp%3B%22%3Dsheet1!B7%22%3C%2FP%3E%3CP%3Ecell4%20%3D%26nbsp%3B%22%3Dsheet1!B8%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20that%20I%20can%20autofill%20the%20formula%20to%20get%20what%20I'm%20after%3F%3C%2FP%3E%3CP%3EApologies%20if%20I%20haven't%20explained%20this%20very%20well%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1288579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288683%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20formula%20series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612786%22%20target%3D%22_blank%22%3E%40tonekil83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20absence%20of%20the%20worksheet%20itself%2C%20the%20one%20you're%20working%20with%2C%20let%20me%20just%20suggest%20you%20investigate%20the%20INDIRECT%20function.%20I%20think%20it%20will%20work%20to%20solve%20your%20need.%20Here's%20some%20of%20the%20Help%20text%20on%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1586272239646.png%22%20style%3D%22width%3A%20407px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182780i7F5879425C288AA7%2Fimage-dimensions%2F407x663%3Fv%3D1.0%22%20width%3D%22407%22%20height%3D%22663%22%20title%3D%22mathetes_0-1586272239646.png%22%20alt%3D%22mathetes_0-1586272239646.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288941%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20formula%20series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612786%22%20target%3D%22_blank%22%3E%40tonekil83%3C%2FA%3EHey%20there%2C%20I%20think%20I%20can%20help%20you.%20Use%20the%20Offset%20Formula.%20In%20Sheet1%20Cell%20C2%20(or%20blank%20adjacent%20Cell)%20input%20a%200%20then%20drag%20it%20all%20the%20way%20down%20to%20the%20end%20of%20your%20data%2C%20set%20to%20fill%20Series%20so%20the%20numbers%20truncate.%20(from%200-100%20or%20whatever%20the%20end%20number%20might%20be).%20Sheet2%20do%20the%20same%20thing.%20In%20Sheet2%20Cell%20B%20enter%20this%20formula%20(Example%20will%20use%20cell%20B2%20in%20Sheet2%20to%20bring%20the%20data%20into%20this%20sheet).%20Adjust%20formula%20as%20needed.%20%3DIF(OFFSET(Sheet1!%24B%242%2CSheet2!C2%2C0)%3D0%2C%22%22%2COFFSET(Sheet1!%24B%242%2CSheet2!C2%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288970%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20formula%20series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612786%22%20target%3D%22_blank%22%3E%40tonekil83%3C%2FA%3EI%20forgot%20something%2C%20or%20actually%20I%20told%20you%20wrong%2C%20in%20Sheet%202%20Cell%20C2%20enter%200%20then%20Cell%20C3%20enter%202.%20Then%2C%20Highlight%20those%20cells%20and%20drag%20them%20down%20to%20fill%20the%20series.%20It%20will%20show%20as%3A%3C%2FP%3E%3CP%3EC2%20%3D%200%3C%2FP%3E%3CP%3EC3%20%3D%202%3C%2FP%3E%3CP%3EC4%20%3D%204%3C%2FP%3E%3CP%3EC5%20%3D%206%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289303%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20formula%20series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612786%22%20target%3D%22_blank%22%3E%40tonekil83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20Sheet2%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20117px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182860iC45E4E059B6FDFB5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Efor%20the%20Sheet1%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%2085px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182861i37807C24B06A59A3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20A5%20enter%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(Sheet2!B%3AB%2C3*(ROW()-ROW(%24A%244)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

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

@tonekil83 

 

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.

mathetes_0-1586272239646.png

 

Highlighted

@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

@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

@tonekil83 

If that's Sheet2

image.png

for the Sheet1

image.png

in A5 enter

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

and drag it down.