Questions

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

+
0 Votes
Locked

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

kicove
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
  • +
    0 Votes
    cfivins

    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)

    +
    0 Votes
    kicove

    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.

  • +
    0 Votes
    cfivins

    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)

    +
    0 Votes
    kicove

    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.