I have 6 rows and only one has a number. I'd like to copy and paste additional rows but when i do it adds 6 to the row number. Example: The A1 cell is =item!A2 then there is 5 blank lines. When I copy and paste the next set of rows I get =item!A8. What I need is =item!A3

Enter this in A1, then copy your group of 6 cells and paste them in A7, and so on. =OFFSET(item!$A$1,(ROW()-1)/6+1,0)

Since the ROW function will return the Row number that the formula resides in,

(ROW()-1)/6+1 will return 1 in A1, 2 in A7, 3 in A13, etc.

For example:

In A1:

ROW() = 1

ROW()-1 = 0

0/6 = 0

0+1 = 1

In A7:

ROW() = 7

7 - 1 = 6

6/6 = 1

1 + 1 = 2

and so on.

That value will then be used as the Row offset from item!A1. With a 0 Column offset, you will see the values from item!A2, item!A3, item!A4, etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

This worked perfectly. Wish I would have asked earlier rather than spend tons of time on it. Thank you so much.

Ask Your Question

Weekly Poll