Copy and paste formula down while automatically changing column reference by 4

[timlh42]

I am trying to copy the following formula:

=IF('Week 1'!$H7,2,"")

and paste it down a column and have to move over 4 columns each time. So when it pastes to the next row it would be

<colgroup><col width="64" style="width: 48pt;"> <tbody> </tbody>

=IF('Week 1'!$M7,2,"")

the next row would be

=IF('Week 1'!$R7,2,"")

the next row would the be:

=IF('Week 1'!$W7,2,"")


Is there any way to do this without having to enter each formula manually?

 

[Joe4]

Sure, you can probably use the use the OFFSET or INDIRECT functions to do that.
We can probably come up with the formula for you, but need to know which cell the first formula is going in.

By the way, moving from column H to M to R to W is incrementing by 5 columns at a time, not 4.

 

[timlh42]

Awesome! The first cell that the formula goes into in E1

 

[Joe4]

OK. Place this formula in cell E1 and copy down:

=IF(OFFSET('Week 1'!$H$7,0,(ROW()-1)*5),2,"")

The keys are the OFFSET function (see: https://exceljet.net/excel-functions/excel-offset-function)
and the ROW() function, which returns the row number that the formula is found in.

 

[timlh42]

Thank You!!!
It works great!!!!

 

[Joe4]

You are welcome.
I hope it makes sense what I did. Feel free to ask any questions about it.