Returning a value every 20 rows from another sheet...

[pasqualebaldi]

Someone had helped me create a formula that would look at another sheet and return a value value from every 20 rows.
This code is in Sheet1 cell F8:F99

Please Login or Register  to view this content.

For example: Sheet1 F8 would return Sheet2 I40 and Sheet1 F9 would return Sheet 2 I60 and so on...


My problem is that I need someone to explain how this code works because now I need to go the other way around. I need Sheet2 BW40 to return Sheet1 F8, Sheet2 BW60 to return Sheet1 F9 and so on...


Can someone help me understand how to do this?

Thank you

[Kaper]

Offset does what it name explains. so to get where you want you shall use in sheet2 BW40:

Please Login or Register  to view this content.

note that only offset in vertical direction is used.

once you write this formula in your BW40 row()value is 40 so (40-40)/20 is 0 then after copying the cell into BW60 you get
(60-40)/20 = 1 so offset 1 from $F$8 and so on.

[bebo021999]

IF you want to drag BW40 down, solution from Kaper is writing Sheet1!F8 in all cells of range BW40:BW59, then write sheet1!F9 in all cells of range BW60:BW79 and so on...

If you want to write in BW40, BW60,...and leave other cells blank, use this in BW40 then drag down:

=IF(MOD((ROW()-40),20)=0,OFFSET(Sheet1!$F$8,(MOD((ROW()-40),20)=0)*INT((ROW()-40)/20),),"")

In case cells other than BW40,BW60... are occupied, copy BW40 to BW60, BW80 only.

[pasqualebaldi]

Offset does what it name explains. so to get where you want you shall use in sheet2 BW40:

Please Login or Register  to view this content.

note that only offset in vertical direction is used.

once you write this formula in your BW40 row()value is 40 so (40-40)/20 is 0 then after copying the cell into BW60 you get
(60-40)/20 = 1 so offset 1 from $F$8 and so on.

Thank you very much! I finally understand how the math works with this formula now.
And I was able cleaned up my original code. It did not need any horizontal offsets:

Please Login or Register  to view this content.