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]

When you post a question of your own in somebody else's thread, especially an old, there's a chance no one will ever see it. TIP... always start your own threads for new puzzles and questions. You can include LINKS in your post to these other threads you find of possible interest.

Sheet1
B3: =INDEX(Sheet2!B:B, CEILING((ROW()+1)/26,1)+1)
C3: =INDEX(Sheet2!C:C, CEILING((ROW()+1)/26,1)+1)
D3: =MID(INDEX(Sheet2!A:A, CEILING((ROW()+1)/26,1)+1), 3, 999)
E3: =INDEX(Sheet2!F:F, CEILING((ROW()+1)/26,1)+1)


Now copy B3:E3 and past in B29:E29, etc.

 

[CLAADA1111]

Re: Dragging down formulas which skip 16 rows to reference another sheet

Hello,

I saw u answered for skipping 8 cells.. can you assist with a formula for skipping 16 cells?
Any help is appreciated!

I have values on Sheet 1, in cells A13 through A2000. 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 A15 = Sheet 1 A13
Sheet 2 cell A31 = Sheet 1 A14
Sheet 2 cell A47 = Sheet 1 A15
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 16 th cell on sheet 2.

 

[jbeaucaire]

Re: Dragging down formulas which skip 16 rows to reference another sheet

This will do it in Sheet1 A13, then copied down:

=IF(MOD(ROW($A$16)-ROW(),16)<>1, "", INDIRECT("'Sheet2'!A" & 13 + FLOOR((ROW()-$A$16)/16,1)))

But I "bet" there is some other piece of information that exists on both sheets that could be used to link these values in a much more direct non-convoluted manner. Some ID, or code? Something?

 

[CLAADA1111]

I have multiple sheets (4), all being tracked to sheet 1, column A (project ID #) , all other values in all sheets are auto populating through VLOOKUP formulas ... but all VLOOKUP are tracked through the project ID ... it's just the one sheet that needs to skip rows ... Imy going to try the value now ... thanks so much ... will let u know...

 

[Peter_SSs]

Re: Dragging down formulas which skip 16 rows to reference another sheet

I have values on Sheet 1, in cells A13 through A2000. 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 A15 = Sheet 1 A13
Sheet 2 cell A31 = Sheet 1 A14
Sheet 2 cell A47 = Sheet 1 A15
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 16 th cell on sheet 2.

If possible, I would try to avoid the volatile function INDIRECT.

I would also be wary of suggestions for this problem that use the ROW() function. Even if they produce the correct answer now, if in the future any rows(s) are added or removed above the formula, it will almost certainly then produce incorrect results.

I think this non-volatile formula in cell A15 of Sheet2, copied down, should do what you have asked.
=IF(MOD(ROWS(A$15:A15),16)=1,INDEX(Sheet1!A$13:A$2000,INT(ROWS(A$15:A15)/16)+1),"")

 

[CLAADA1111]

Re: Dragging down formulas which skip 16 rows to reference another sheet

If possible, I would try to avoid the volatile function INDIRECT.

I would also be wary of suggestions for this problem that use the ROW() function. Even if they produce the correct answer now, if in the future any rows(s) are added or removed above the formula, it will almost certainly then produce incorrect results.

I think this non-volatile formula in cell A15 of Sheet2, copied down, should do what you have asked.
=IF(MOD(ROWS(A$15:A15),16)=1,INDEX(Sheet1!A$13:A$2000,INT(ROWS(A$15:A15)/16)+1),"")

-------------------------------------

So Far Not Working ... "Sheet1" is actually named "Active Opportunities" ... I tried editing the name in the formula, but no luck as of yet

 

[CLAADA1111]

Re: Dragging down formulas which skip 16 rows to reference another sheet

So Far Not Working ... "Sheet1" is actually named "Active Opportunities" ... I tried editing the name in the many formula's given so far, but no luck as of yet ... uuuughhh

I shared (2) Images/ScreenShots of the pages I am trying to format

Thanks everyone for the help

 

[Peter_SSs]

Re: Dragging down formulas which skip 16 rows to reference another sheet

So Far Not Working ...

That doesn't give us much to go on - a bit like going to the doctor and saying nothing except "I don't feel well".

- Did it give an error message? What?

- Did it return the wrong result(s)? Explain what it returned and what it should have returned.

- Something else?

"Sheet1" is actually named "Active Opportunities"

OK, here is my 'Active Opportunities' with some dummy data indicating the cell it is in.

Excel 2010 32 bit
	A
13	A13
14	A14
15	A15
16	A16
17	A17
18	A18
19	A19
20	A20
21	A21
22	A22
23	A23
24	A24
25	A25
26	A26
Active Opportunities

And here is my Sheet2 with the formula that I dragged down.

Is this what you want/expect? If not, more details please.

Excel 2010 32 bit
	A
15	A13
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31	A14
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47	A15
48
Sheet2

Cell Formulas
Range	Formula
A15	=IF(MOD(ROWS(A$15:A15),16)=1,INDEX('Active Opportunities'!A$13:A$2000,INT(ROWS(A$15:A15)/16)+1),"")

 

[CLAADA1111]

Re: Dragging down formulas which skip 16 rows to reference another sheet

That doesn't give us much to go on - a bit like going to the doctor and saying nothing except "I don't feel well".

- Did it give an error message? What?

- Did it return the wrong result(s)? Explain what it returned and what it should have returned.

- Something else?


OK, here is my 'Active Opportunities' with some dummy data indicating the cell it is in.

Excel 2010 32 bit

	A
A13
A14
A15
A16
A17
A18
A19
A20
A21
A22
A23
A24
A25
A26

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

</tbody>

Active Opportunities​

And here is my Sheet2 with the formula that I dragged down.

Is this what you want/expect? If not, more details please.

Excel 2010 32 bit

	A
A13
A14
A15

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A15[/TH]
[TD="align: left"]=IF(MOD(ROWS(A$15:A15),16)=1,INDEX('Active Opportunities'!A$13:A$2000,INT(ROWS(A$15:A15)/16)+1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Alright ... this worked and thank you (Issue was my Excel auto formatted to RC1 & was inhibiting formula) ... After seeing it work I had to adjust to actually skip 17 rows, I was able to make adjustments, but my understanding of the formula in totality is still rookie status
So my next challenge is this:
I now need this formula to skip the same 17 rows Starting at "B15"... =VLOOKUP(A15,'Active Opportunities'!13:2000,4,False) ... image attached for better view

 

[Peter_SSs]

Re: Dragging down formulas which skip 16 rows to reference another sheet

1. Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

2. Your image seems to just be an image of a forum post. I don't understand what it was trying to show. In any case you are generally better to show us screen shots of your worksheet(s), with dummy data if sensitive. And in that case it is much preferred to use a method like I did above rather than a pure image so that sample data can be copied to a worksheet to test.

3. Please clarify where the formulas need to go and exactly what they need to do. I am unsure exactly what this means: "need this formula to skip the same 17 rows Starting at "B15""