formula to use next cell in line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I need to copy a row and paste it several rows down. I have a formula
='Sheet1'!A5 in row 6, and if I try and paste it 10 rows down in row 16, it
changes to ='Sheet1'!A15 The problem is, I need it to say ='Sheet1'!A6 so is
there a formula I can use that adds only one cell down, instead of the row
number its being pasted on?

I have a list of titles on Sheet1 that I need to be put in rows on Sheet2
and have lines to sign up underneath, but I don't want to have to change each
one to the next cell on sheet1.
 
You could copy it to successive rows (i.e. row6, row7 etc) and then
insert 9 rows between 5 and 6, between (the old) 6 and 7, and so on.

There are other ways, using INDIRECT or OFFSET formulae (see Excel
Help).

Hope this helps.

Pete
 
That would not work, as on sheet2 there are 3 rows, then 10 blank, then 3
more rows, then 10 more blank. on Sheet1, they are all successive.

But thank you anyway!
 
Enter this formula in Row6 of Sheet2:

=INDEX(Sheet1!A:A,ROWS($1:50)/10)

Then, enter this formula in Row16 of Sheet2 in that same column:

=INDEX(Sheet1!A:A,ROWS($1:60)/10)

NOW, select the Row6 cell containing the formula,
Hold down <Shift>,
And click in the Row25 cell in that column.

This will select the range of Row6 to Row 25, with your 2 formulas included
in row 6 and 16.

Now, click on the fill handle of that 20 row selection, and drag down as
needed.
 
Back
Top