# Drag horizontally, increase reference vertically



## Young Grasshopper (Dec 14, 2022)

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 (Dec 14, 2022)

How about
	
	
	
	
	
	



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


----------



## arthurbr (Dec 14, 2022)

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 (Dec 14, 2022)

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 (Dec 14, 2022)

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


----------



## Fluff (Dec 14, 2022)

arthurbr said:


> 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 (Dec 14, 2022)

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


----------



## Fluff (Dec 14, 2022)

So what is in D5:G5?


----------



## Young Grasshopper (Dec 14, 2022)

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 (Dec 14, 2022)

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))
```


----------



## Young Grasshopper (Dec 14, 2022)

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


----------



## Young Grasshopper (Dec 14, 2022)

You make it look easy, Fluff!
Works like a dream, and saved me a ton of work
Thank you and enjoy your evening


----------



## Fluff (Dec 14, 2022)

You're welcome & thanks for the feedback.


----------



## arthurbr (Dec 15, 2022)

Fluff said:


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


Unless B11 already contains the null string ?


----------



## ghunderassi (Dec 15, 2022)

@Fluff dont get me wrong i was here to help. Just wanted to make my solution when i saw you were already faster. So i tried your solution and gave a fast feedback. Thanks for the solution!


----------



## Fluff (Dec 15, 2022)

My apologies, I had misread the question & thought you were asking for something different than the OP.


----------

