CODEWITHSUNDEEP
excelexcel-formula
Nate Walker
Nate Walker

Reputation: 217

Copying Excel Formula Using Pattern For Columns and Rows

I have an excel worksheet that contains a series of formulas in F2:F4. Cell F2 contains the formula =Sheet3!Z4, cell F3=Sheet3!Z6, cell F4=Sheet3!Z8. I able to successfully account for the +2 in rows when I drag the formula down by using this offset formula. 

=OFFSET(Sheet3!$Z$4,(ROW()-2)*2,0)

Is it possible to alter this formula so that I can drag across the columns and down the rows? For example, if I dragged across from F2 to G2, G2 would return =Sheet3!AA4, but if I dragged from F2 to F3, I would still be able to get =Sheet3!Z6. I can't unlock the Sheet3!$Z$4 in the formula above to drag across columns because if I tried to drag down the rows, my reference number would keep changing which would throw off the entire formula. Does anyone know how to rewrite this formula so that excel can recognize the pattern (+2 in rows, +1 in columns) I'm trying to copy when I drag the formula? Thanks in advance!

Upvotes: 0

Views: 929

Answers (1)

user4039065
user4039065

Reputation:

try this in F2 then drag down and right,

=index(Sheet3!z:z, (row(1:1)-1)*2+4)

Upvotes: 1

Related Questions