# Software

## Question

Locked

### How create a formula to copy but have the row change by fixed number?

By ·
I need to extract data from worksheet called "KI" to another worksheet called "Summary". So for the first reference I used this formula ='KI 2011'!L16 The next cell I need to reference ='KI 2011'!L23 I need to repeat this many times but the only change is the row number which ALWAYS increases by 7, so next reference would be ='KI 2011'!L30 and so on. Is there anyway to do this automatically with a formula so I don't have to keep selecting the cells hit = then go to the KI worksheet select the cell and hit enter? It would save alot of time from having to do that over 100 times. Thank you.
Using Excel 2007

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)
| Thread display: Collapse - | Expand +

Collapse -

### How create a formula to copy but have the row change by fixed number?

by In reply to How create a formula to c ...

One way would be to use the index function. The following assumes that the destination cells are in a contiguous verticle range.

=INDEX(abs_ref_to_source_range,7*(ROW(current_dest_cell)-ROW(abs_ref_to_first_cell_in_dest_range))+1)

Collapse -

### How create a formula to copy but have the row change by fixed number?

by In reply to How create a formula to c ...

Thank you. My knowledge of Excel does not include INDEX function. I did some research but am still confused. The destination cells are in a contiguous vertical range. Can you be a bit more detailed. If my 1st destination cell is in sheet Summary B1 and the info needed comes from 'KI 2011'!L16, and my 2nd destination cell is Summary B2 and the info comes from 'KI 2011'!L23 what exact formula would be entered in cells B1 and B2? The range of cells that contains the needed info are in sheet KI 2011 from L1 thru L400. Thanks.

3 total posts (Page 1 of 1)

## Related Discussions

• 0

#### Desktop Softwares

Techno Factz //

• 8

#### what is the best image viewer for Windows 10?

newminionphowz //

• 0

#### Notedpad++ missing from the context of mouse right click

zillah2004 //

• 0

#### DO NOT Send to a Friend (9 years later) unless you like Star Wars spam

whitehatdefender //

• 4

#### Searching for a professional Apple Music Converter

matthewwalker531 //