Apr 07 2020 07:30 AM
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
Apr 07 2020 08:11 AM
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.
Apr 07 2020 09:15 AM
@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))
Apr 07 2020 09:20 AM
@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
Apr 07 2020 10:49 AM
If that's Sheet2
for the Sheet1
in A5 enter
=INDEX(Sheet2!B:B,3*(ROW()-ROW($A$4)))
and drag it down.