Dragging down formulas which skip 8 rows to reference another sheet

csilabgirl

Active Member
Joined
Aug 14, 2009
Messages
359
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Put this in Sheet2 cell A28 and copy down:

=IF(MOD(ROW()-4,8)=0, INDIRECT("Sheet1!A" & 8+((ROW()-4)/8)-2), "")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,259
Messages
6,177,485
Members
452,782
Latest member
ZCapitao

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top