# how to copy a formula that skip rows?



## xlntmgr

Im trying to copy a cell from a worksheet that skips rows (every seven rows) into another worksheet that doesn't skip rows.

For example, I have values/formula in a worksheet from:
K8
K15
K29

that I want to copy in another worksheet at:

E5
E6
E7

I have hundreds of rows to copy so doing it manually isn't practical.  Please advise.


----------



## mgm05267

Hi...

Welcome to the Board...

Have you tried filtering the column in first sheet, copy & paste in another sheet!!??


----------



## Andrew Poulsom

Welcome to MrExcel.

Example:

=INDEX(Sheet2!K:K,(ROWS(A$1:A1)*7)+1)


----------



## T. Valko

xlntmgr said:


> Im trying to copy a cell from a worksheet that skips rows (every seven rows) into another worksheet that doesn't skip rows.
> 
> For example, I have values/formula in a worksheet from:
> K8
> K15
> K29
> 
> that I want to copy in another worksheet at:
> 
> E5
> E6
> E7
> 
> I have hundreds of rows to copy so doing it manually isn't practical. Please advise.


Is K29 supposed to be K22?

Try this...

Entered in E5 and copied down:

=OFFSET(K$8,ROWS(E$5:E5)*7-7,0)


----------



## xlntmgr

Thanks T. Valko.  Your recommendation worked.


----------



## T. Valko

xlntmgr said:


> Thanks T. Valko. Your recommendation worked.


You're welcome. Thanks for the feedback!


----------



## mgm05267

Hi...

This formula holds good for regular offsets (7 cells).

How to get the values from irregular offsets...??

Please help...


----------



## T. Valko

mgm05267 said:


> Hi...
> 
> This formula holds good for regular offsets (7 cells).
> 
> How to get the values from irregular offsets...??
> 
> Please help...


Can you provide an example?


----------



## trommann1974

With =OFFSET(K$8,ROWS(E$5:E5)*7-7,0) as the formula, how can i add an IF statement so that if K8 is blank it will show blank instead of "0"?


----------



## steveuk87

trommann1974 said:


> With =OFFSET(K$8,ROWS(E$5:E5)*7-7,0) as the formula, how can i add an IF statement so that if K8 is blank it will show blank instead of "0"?



I think what your asking for would just be a simple IF statement isnt it?:



		Code:
__


=IF(K8="","",=OFFSET(K$8,ROWS(E$5:E5)*7-7,0))


Correct me if i'm wrong..


----------



## xlntmgr

Im trying to copy a cell from a worksheet that skips rows (every seven rows) into another worksheet that doesn't skip rows.

For example, I have values/formula in a worksheet from:
K8
K15
K29

that I want to copy in another worksheet at:

E5
E6
E7

I have hundreds of rows to copy so doing it manually isn't practical.  Please advise.


----------



## jefraz

Is it possible to reverse this.  For example if my data didn't skip but the cells that I needed to input data did?
Data is in K5:K10 and I need the data from K5 in a new worksheet A25, then K6 in A49, then K7 in A73 etc...

Thanks!!


----------



## MountainKris

jefraz said:


> Is it possible to reverse this.  For example if my data didn't skip but the cells that I needed to input data did?
> Data is in K5:K10 and I need the data from K5 in a new worksheet A25, then K6 in A49, then K7 in A73 etc...
> 
> Thanks!!



Jefraz et al,

Was this question ever solved? I am having the exact same problem. Thanks a million.

Kris


----------



## MountainKris

T. Valko said:


> Is K29 supposed to be K22?
> 
> Try this...
> 
> Entered in E5 and copied down:
> 
> =OFFSET(K$8,ROWS(E$5:E5)*7-7,0)



T. Volko - is there a way to reverse this? Say you were going from data in E5:E7 but you want to copy in K8, K15, K29.

Thanks!


----------



## Smedellinr13

Hi now can you help me how to do the same but in reverse. I meant...
I have in sheet1
D1
D2
D3
D4

that need to be reflected on Sheet2
C4
C24
C44
C64

thanks


----------

