How to tweak a reference so that when it's copied one column over, the reference jumps by five columns?

[alexcr]

Let's say Cell A1 in "WorksheetA" references Cell A5 in "WorksheetB".

When you copy Cell A1 to Cell B1, its formula would of course typically adjust to reference Cell B5.

However, let's say I'd instead like its reference to adjust by five columns, not one column. (That is, without changing the fact that I'm copying and pasting from Cell A1 to Cell B1.)

So copying the formula in Cell A1 to Cell B1 would update the reference from Cell A5 to Cell F5. And subsequently copying that formula from Cell B1 to Cell C1 could update the reference from Cell F5 to Cell K5.

What formula can I use to accomplish this? (Ideally a formula that would work regardless of what the starting row and column are.) Thanks in advance!

 

[jasonb75]

Try

=INDEX(WorksheetB!5:5,(COLUMNS($A1:A1)-1)*5+1)

 

[alexcr]

Try

=INDEX(WorksheetB!5:5,(COLUMNS($A1:A1)-1)*5+1)

I tried this out and it seems like it could do the trick. Thank you!