And How do we apply if the Colomn A is in a different Sheet in the same workbook?
Just add the sheet reference in front of the cell in the formula, i.e.
=IF(MOD(COLUMN(),5)=2,OFFSET(Sheet1!B1,INT(COLUMN()/5),1-COLUMN()),"")
To understand the formula, it is important to understand the various functions used it in.
The
COLUMN() function simply returns the column that the formula is placed in. So, if you typed =COLUMN() in any cell, copy it across a row and watch what it returns,
The
MOD function returns the remainder when one value is divided by another.
The
INT function returns the integer value when one number is divided by another (drops the decimal part).
With
OFFSET function, you give it a starting cell address, then tell it how many rows and columns to move from it.
You want to put these values in columns 2, 7, 12, 17, 22, etc. So it is every 5th column, starting with column 2.
So, in our IF function, we are saying that if the remainder of the column number divided by 5 is 2, then return the value of the next formula in that cell, else return nothing ("").
Next, with the OFFSET function, I tell it to start from the cell the formula is in (B1), and then to determine how many rows/columns to move from there:
Rows: Divide column number by 5, and drop the decimal portion. So, 2/5 would return 0; 7/5 would return 1; 12/5 would return 2, etc.
Columns: Subtract the column number from 1. So 1-2=-1; 1-7=-6;1-12=-11 (note the negative number means we move to the left ,and not the right).
So, if you do the math on the first few:
B1: OFFSET(B1,0,-1) means that starting from cell B1, we move 0 rows and 1 column to the left, which is cell A1. So we are returning the value from cell A1 in cell B1.
G1: OFFSET(G1,1,-6) means that starting from cell G1, we move 1 row down and 6 columns to the left, which is cell A2. So we are returning the value from cell A2 in cell G1.
L1: OFFSET(G1,2,-11) means that starting from cell G1, we move 2 row down and 11 columns to the left, which is cell A3. So we are returning the value from cell A3 in cell L1.
etc.
I hope that clarifies things for you. The trick to figuring out complicated formulas like this is to break it down into smaller parts, and figure out what each part is doing.
