How do i skip columns in a formula?

Question

I am creating a formula that takes information from one worksheet and then is populated in 1 column on another worksheet. The information in the 1st worksheet is populated every 4th column. So i need to just simply take that information in every 4th column and populate it into 1 column on the 2nd worksheet. So essentially taking data from every 4th column in a single row, and placing them in sequence across 12 rows in a single column. I cannot seem to figure out how to skip the 3 columns. It just moves to the next cell no matter what i try. I've messed with the formula and either get 0, -1, or Errors.

1st picture is the data i'm trying to populate into the column below. The pink number is the desired data to match. But instead of skipping the 4 cells to get the next value, it stay within the column. I've tried adding row too but not seeming to catch what's wrong. So in the case below. Starting cell is g26 and instead of going to K26, it goes to g27. I cannot seem to get it to skip those 3 columns however i try the formula.

This is an example of one of the formulas i tried. I also switched Column for Row and same result. =OFFSET($E$20,0,COLUMNS('Wt. Haul'!G26)*4)-1 - referencing the other worksheet. As seen with E20 i tried to offset from the top value below "Mileage". I also had entered the worksheet as the reference I.e. =OFFSET('Wt. Haul'!G26,0,COLUMNS('Wt. Haul'!G26)*4)-1. Cannot figure out how to just populate that 4th cell into the summary column. Any help would be great.

Answer 1

Hi,

If the 4th cells is a numeric cell, then this formula will work in cell D6

=TRANSPOSE(FILTER(D3:O3,ISNUMBER(D3:O3)))

Answer 2

Try this:

=OFFSET('Wt. Haul'!$G$26, 0, 4*(ROW()-3))

This assumes that the first formula is in row 3. Adjust the -3 if the first formula is in another row.

Answer 3

So the cell data that needs to change is the g26, so the following cell in the column needs to have a K26 for next cell, then the third cell would be the value from O26 and so on. I am attempting to pull data from 26 row every 4th column and populate the data in each succints row on the Mileage column on the 2nd sheet. Each time i get a successful formula it changes G26 to g27, then g28 etc. The formula you provided ends with #REF! or #Name?.

I need the cell g26 to then move over 4 columns to take the value from cell K26. Can't seem to figure how to set the formula to automatically put that cell in next when i got to drag down the formula in the lower mileage column.

Answer 4

Please try it!

Wt. Haul sheet:

Other sheet:

It works! You only need to adjust the -3 if your first formula is not in row 3.

An alternative formula would be

=INDEX('Wt. Haul'!$26:$26,4*(ROW()-3)+7)

As with the other one, adjust the -3 if needed. The +7 is fixed; it is the column number of G26.