CODEWITHSUNDEEP
exceloffset
user2286723
user2286723

Reputation: 21

How do I formulate offset function for nth column in different sheet

I've been struggling with this all day, though I'm sure it's not impossible.

I have a sheet with a value in every 3rd column and I need to copy this value into another sheet, ignoring the 1st and 2nd column.

So for: A1, B1, C1, D1, E1, F1 in sheet 1

I just need C1 , F1 in sheet 2

Is there a formula that I can drag across all columns? This has to happen for rows 1 - 150 as well.

Upvotes: 2

Views: 23605

Answers (1)

David Zemens
David Zemens

Reputation: 53623

This should do it:

=OFFSET(Sheet2!A1,0,COLUMN()*2,1,1)

Modify the sheet name as necessary.

Use the argument Column()*2 for the column offset. This takes the integer value of the current column number, and multiplies it by 2. So the offset for column A is 2, which refers to column C, the offset for column B is 4, which refers to column F, etc.

Upvotes: 5

Related Questions