Dragging down formulas which skip 8 rows to reference another sheet

[csilabgirl]

Hello,

I have values on Sheet 1, in cells A9 through A400. On sheet 2, I want to be able to reference the values on Sheet 1, however the formula is every 8 rows. Meaning:

Sheet 2 cell A28 = Sheet 1 A9
Sheet 2 cell A36 = Sheet 1 A10
Sheet 2 cell A44 = Sheet 1 A11
and so on....

I want to be able to drag down the formula on sheet 2, so that I dont have to manually type the cell reference in every 8th cell on sheet 2.

I hope that makes sense. If anyone has any ideas, that would certainly save me a lot of time.

Thanks

 

[jbeaucaire]

Put this in Sheet2 cell A28 and copy down:

=IF(MOD(ROW()-4,8)=0, INDIRECT("Sheet1!A" & 8+((ROW()-4)/8)-2), "")

 

[csilabgirl]

WOW! That works great, you saved me so much time. Thank you. one more question. I want to do the same thing for column B, where Sheet 2 A32 = Sheet 1 cell B9. Again, it will skip 8 rows. I tried substituting the "A" in your formula with "B", but that did not work

 

[Robert Mika]

Change as well -4 to -8.
=IF(MOD(ROW()-8,8)=0,INDIRECT("Sheet1!b"&8+((ROW()-8)/8)-2),"")

 

[csilabgirl]

Great, thank you.

 

[RoryA]

FYI, you could also enter:
Sheet 2 cell A28: ZSheet1!A9
Sheet 2 cell A36: ZSheet1!A10

then select A28:A43 and copy down as far as required (it will fill in ZSheet1!A11 and so on every 8th row). Then just do a Find&Replace replacing Z with = to convert to a formula.
FWIW.

 

[Elmaln]

Change as well -4 to -8.
=IF(MOD(ROW()-8,8)=0,INDIRECT("Sheet1!b"&8+((ROW()-8)/8)-2),"")

Could you please explain the reasoning behind the formula? I am trying to recreate it so that dragging down a cell in one sheet would would reference a cell 24 rows down on another sheet. Sadly I have not been able to recreate that using this formula as I don't understand how it works. Any help would be greatly appreciated!

 

[jbeaucaire]

Functions you will need to read up in the F1 Help Files:


=ROW() - when entered in a cell on row 10, would return the result of 10. =ROW(A3) entered in any cell would always return a 3.

=MOD() - hard to explain, the help files will do better.

=INDIRECT() - manually string pieces of text together to create a reference, then use that reference to retrieve a value from another sheet

Also, if you have any cell with a formula in it, you can use the Evaluate Formula function on the Formula tab to watch the formula unfold one calc at a time.

 

[myfast]

I also do not quite understand how this formula works. Can someone help me with the formula for the following?

I would like to input all the formula in sheet 1.
I would like to have B3 from sheet1 to equal B2 from sheet2, B29 from sheet1 to equal B3 from sheet2, and so on. (so skip every 26 rows)

I would like to have C3 from sheet1 to equal C2 from sheet2, C29 from sheet1 to equal C3 from sheet2, and so on.

I would like to have E3 from sheet1 to equal F2 from sheet2, E29 from sheet1 to equal F3 from sheet2 , and so on.

For D3 from sheet1, I would like to equal A2 from sheet2 except take the first two text out from sheet2. For example, A2 from sheet2 might say AA0321 or BB458910, but I want it to show as 0321 or 458910 on D3 in sheet1. I would like to continue the same pattern for D29 sheet1 to equal A3 from sheet2 without the first two text and so on.

 

[myfast]

Anybody?