Drag down but offset the column value

[I.am.Rustam]

Hi guys,

I need help figuring out a formula to offset a reference.

I need to get a value from column C1 (different sheet) into A1. I need to drag down A1, but value I need from D1, E1, F1, G1, H1 etc.

So,

A1 = C1
A2 = D1
A3 = E1
A4 = F1
etc.

Any ideas? Thanks a lot!

[José Augusto]

Try this in Sheet1!A1 and copy down

Please Login or Register  to view this content.

[Tony Valko]

Try this...

Entered in A1:

=IFERROR(INDEX(Sheet1!C$1:Z$1,ROWS(A$1:A1)),"")

Adjust for the correct end of range where I've used Z$1.

Copy down until you get blanks.

[I.am.Rustam]

Thank you Jose for your prompt response! Your formula works great.

I also was able to figure out Offset formula. So, for my case it is OFFSET(Sheet2!$C$1,,ROW()-1)

I try to avoid index formulas since they slower my spreadsheet.

Thanks again! Appreciate it.

[I.am.Rustam]

Thanks Tony! It works great as well!

[Tony Valko]

You're welcome. Thanks for the feedback!

[José Augusto]

Hi Rustam
I am convinced that is the use of OFFSET that will make your slower sheets, not the INDEX.
Avoid intervals referencing entire rows or entire columns
So, in my formula you should use =INDEX(Sheet2!$A$1:$Z$1,1,ROW($A3)) adjusting needs.

[I.am.Rustam]

Oh, that could be the reason. Whenever I used Index, I would reference the entire column/row.
Got it. Thanks again Jose for your feedback!

Everyday we learn something!
Thanks!

[José Augusto]

Thanks for feedback and rep.