Drag horizontally, increase reference vertically

[Young Grasshopper]

Another day, another problem!

I have this code in C5: =IF('Sheet1'!B11="";"";'Sheet1'!$B11)
I need to drag C5:G5 horizontally, so this could would be copied to every 5th column, but the reference cells must increase by one vertically every time.
Like this:
C5: =IF('Sheet1'!B11="";"";'Sheet1'!$B11)
H5: =IF('Sheet1'!B12="";"";'Sheet1'!$B12)
M5: =IF('Sheet1'!B13="";"";'Sheet1'!$B13)
etc.

How would I go about this?
Would appreciate any help

 

[Fluff]

How about

=IF(INDEX(Sheet1!$B:$B,COLUMNS($C5:C5)+10)="","",INDEX(Sheet1!$B:$B,COLUMNS($C5:C5)+10))

 

[arthurbr]

To me

=IF('Sheet1'!B11="";"";'Sheet1'!$B11)

and

='Sheet1'!$B11

are exactly the same thing, so why make things more difficult than they are ?
So , supposing Fluff's answer is correct ( the contrary would be astonishing) just use

= INDEX(Sheet1!$B:$B,COLUMNS($C5:C5)+10)

 

[ghunderassi]

Hi @Fluff
I have tried your formula, but for me empty columns are counted as well meaning it looking for B16 in column H

 

[Young Grasshopper]

Yes, as Ghunderassi said. It would work perfectly if the formula were to be in adjacent cells, but not when skipping cells.

 

[Fluff]

are exactly the same thing, so why make things more difficult than they are ?

No they aren't, if B11 is blank you would get a 0 instead of a blank.

 

[Fluff]

@ghunderassi Please do not hijack other people's threads.
If you have a question, start a thread of your own.

 

[Fluff]

So what is in D5:G5?

 

[Young Grasshopper]

In D5 it's just the same formula as C5, but with reference cell C instead, so; =IF('Sheet1'!C11="";"";'Sheet1'!C11)
E5 is blank, but is going to be used to just write in, F5 is blank but have a data validation list, G5 is just blank.

 

[Fluff]

In that case use

=IF(INDEX(Sheet1!$B:$B,INT((COLUMNS($C5:C5)-1)/5)+11)="","",INDEX(Sheet1!$B:$B,INT((COLUMNS($C5:C5)-1)/5)+11))