Pattern for Cell value equal to cells at different instances

[Xlitup]

Hey Peeps,

This one is a bit hard to explain for me and that's why the title is a bit weird.

I hope someone can help me out on this as this will ease up things.

I would like to have the following:

If A1 = B2 and A2 = B42
So as you can see the pattern is of every 40 cells. Is there a formula that I can use for the other cells too,

Something like A3=(A2+40Rows)
And A4=(A3+40rows)

Let me if it's not clear enough and will try to explain it better.

 

[Peter_SSs]

Let me if it's not clear enough and will try to explain it better.

Tell us more about
- What you have and where
- What you want and where

 

[Xlitup]

Hey Peter,

On sheet1:

Column A
Sheet1 A1=Sheet2 B1
Sheet1 B1=Sheet2 B41
Sheet1 C1=Sheet2 B82

And so on for x amount of columns. How can I use the dragging of the cells as the pattern cannot be recognized by excel. So is there a formula to have cells be equal to every 40th row?

 

[lrobbo314]

This formula in A1 on Sheet 1 then dragged down.

=INDIRECT("Sheet2!B" &((ROW()-1)*40)+2)

 

[Peter_SSs]

Hey Peter,

On sheet1:

Column A
Sheet1 A1=Sheet2 B1
Sheet1 B1=Sheet2 B41
Sheet1 C1=Sheet2 B82

And so on for x amount of columns. How can I use the dragging of the cells as the pattern cannot be recognized by excel. So is there a formula to have cells be equal to every 40th row?

Please be careful with your descriptions and example as they are all we have to try to understand what you have and what you want. So far it has been somewhat confusing.

- In the first post you wanted the results in A1, A2, A3 etc (down a column). This time you are saying results in A1, B1, C1 etc (across a row). I will assume down a column as in post 1
- The red numbers here are not a uniform 40 rows as you described earlier. 1-41 is 40 rows but 41-82 is 41 rows. I will assume a uniform 40 rows.

This avoids the volatile function INDIRECT that was used in the previous suggestion.
In cell A1 of Sheet1 and copied down:
=INDEX(Sheet2!$B$1:$B$1000,(ROWS($A$1:A1)-1)*40+1)

Adjust the $1000 in this formula to match your data range in Sheet2

If you are, in fact, wanting to drag this across, change ROWS to COLUMNS in the formula.

 

[Xlitup]

Thanks guys. Peter I purposely put in Columns instead of rows as any method would suit me. But thanks for the Indirect function idea as could manipulate it according to my need and it worked like a charm.

Cheers.