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.
