Copy formula down column but need to skip 'n' rows

[wschultek]

Hello!

I have been looking around but have been unable how to figure my problem:

I have two workbooks. One workbook serves as a tracker for dates while the other one uses the dates inputted in the first workbook to track futher information based on that date.

When copying this formula I need to 5 rows skipped to pull in the correct - example below:

D6='[662 Foundation Tracking.xls]Spread Footing'!$AR5
D13='[662 Foundation Tracking.xls]Spread Footing'!$AR6
D20='[662 Foundation Tracking.xls]Spread Footing'!$AR6

but instead I am getting:

D6='[662 Foundation Tracking.xls]Spread Footing'!$AR5
D13='[662 Foundation Tracking.xls]Spread Footing'!$AR12
D20='[662 Foundation Tracking.xls]Spread Footing'!$AR19

Does anyone have idea on how to fix this?

Thanks for any help.

[NBVC]

In D6 enter:

=IF((MOD(ROW(),7)-1)/5=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$AR:$AR,INT(ROW()/7)+5),"")

copied down.

[wschultek]

Perfect - Thank you!

[NBVC]

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[NBVC]

From PM:

I am trying to use the string you posted in the forum to adjust the colmun.

=IF((MOD(ROW(),7)-1)/5=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$AR:$AR,INT(ROW()/7)+5),"")

What would I need to change if I need to pull from column from BG?

I tried:

=IF((MOD(ROW(),7)-1)/5=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT(ROW()/7)+5),"")

But it doesn't seem to pull in any information.

Any ideas?

Thanks again for the help.

That should work if you are putting the formulas in the same rows, ie. start in E6

[wschultek]

My question was misworded -

How do I adjust the starting row -

Say instead of starting in D6 I need to start in D11

[NBVC]

What is the first cell it needs to refer to in column BG?

[wschultek]

First cell is:

='[662 Foundation Tracking.xls]Spread Footing'!$BG$5

[NBVC]

Does this do it?

=IF((MOD(ROW(),12)-1)/10=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT(ROW()/12)+5),"")

[wschultek]

Yes it does!

Thanks again.

=IF((MOD(ROW(),12)-1)/10=1 - Do you have time to explain the change from 7 to 12 and from 5 to 10?

Just trying to figure logic out for in the future.

Spread Footing'!$BG:$BG,INT(ROW()/12)+5),"") - Does the 12 reference back to the first part of the statement?

[NBVC]

If you put this part of the formula in Row 1 of an empty column and copy it down: =(MOD(ROW(),12)) you will see that it starts with 1 and at each 11th row you get an 11.. and after that every , the 12 rows later you get 11 again and so the sequence repeats, in that pattern.

Now if you subtract 1: =(MOD(ROW(),12))-1 and copy down those 11's turn to 10's... which you can divided by 10 to get 1 as in =((MOD(ROW(),12))-1)/10. So copy that down and you get 1 at row 11, 23, etc.. The initial part of the formula checks for a 1 then and continues, otherwise it leaves it blank... in hindsight it may be easier to reduce this part of the function down to: =(MOD(ROW(),12))/11=1 instead and get the same results... (so the 12 represents 1 row more than the start row and the 11 is the start row. And could have done similarly with the first formula: i.e. =(MOD(ROW(),7))/6=1. As a matter of fact, if we wanted to we could have reduced it even further and not divide at all so that you end up with =(MOD(ROW(),7))=6 and =(MOD(ROW(),12))=11 for the respective formulas.

In this part: INDEX(A:A,INT(ROW()/12)+5),"") the 12 is the same 12 as the first part, so the INT(Row()/12) part gives the Integer portion of the ROW number you are in divided by 12.. which will be 0 until Row 11, then 1 until row 23, and so on... add 5 gets you to your first row number to extract... row 5 and the INDEX() indexes the column and extract the 5th row and copied down at row 23, it get row 6, etc...

[wschultek]

Seems like the second cell D18 isn't working using:

=IF((MOD(ROW(),12)-1)/10=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT(ROW()/12)+5),"")

The sequence that I need is as follows:

D11='[662 Foundation Tracking.xls]Spread Footing'!$BG5
D18='[662 Foundation Tracking.xls]Spread Footing'!$BG6
D25='[662 Foundation Tracking.xls]Spread Footing'!$BG7

Can you help one more time?

[NBVC]

Try:

=IF(MOD(ROW(),7)-3=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT(ROW()/12)+5),"")

[wschultek]

Returns a 0 and changes the formula to the following:

=IF(MOD(ROW(),7)-3=1,INDEX('\\192.168.1.10\scanfolder\Foundations\[662 Foundation Tracking.xls]Spread Footing'!$BG$1:$BG$65536,INT(ROW()/12)+5),"")

[NBVC]

Try, row D11:

=IF(MOD(ROW(),7)-3=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT((ROW()+3)/7)+3),"")

[wschultek]

Well it worked the second time I tried the formula but it repeats the same date

D11 = BG5
D18 = BG6
D25 = BG7
D32 = BG7
D39 = BG8
D46 = BG8
D53 = BG9
D60 = BG10
D67 = BG10

[wschultek]

Try, row D11:

=IF(MOD(ROW(),7)-3=1,INDEX('[662 Foundation Tracking.xls]Spread Footing'!$BG:$BG,INT((ROW()+3)/7)+3),"")


This fixes the problem - Again thanks for the help.

[Xolin_]

So i have a similar issue, but I cant seem to adjust my formula to match what im trying. Here is an example:

Please Login or Register  to view this content.

This pattern needs to repeat several hundred times (its a setup for a test case tracker)
Everything in <Carrots> is linked from another sheet, >E< are empty cells. I got it to link, but the increments would not match properly

EI

|<=B3>
|>E<
|>E<
|<=B6>

and I need it to be

|<=B3>
|>E<
|>E<
|<=B4>

Also for the

|Test this: <pull from other sheet>

I know its (if im pulling in from F1

=CONCATENATE("Test this:", F1)

but I'm getting that same increment error from before.

plz Halp!
....and thanks in advance!

[Xolin_]

HOLY SPACE-BALLS BATMAN!!!

i figured out all but the:

=CONCATENATE("Test this:", F1)


......Now i just need to try to get that and we are GOLDEN!

BTW here's my answer - if calling row F from other sheet, increment +1 every 3 cells:

=IF((MOD(ROW(),3)+1)=3,INDEX('[OTHER-SHEET.xlsx]Sheet1'!$F:$F,INT(ROW()/3)+1),"")