copy ref cell to next row incremented by 3

=INDEX(A:A,3*ROW(A2))

and copy down, change range as needed

Oh hey, and an even more efficient formula:

=INDIRECT(CHOOSE(COLUMN(A1),"A","D","F","G","H","E")&3*ROW(A2))

No need to change ranges or references. Just copy and drag all over.

Attach a sample workbook. Many users are unable to view images attached in this forum due to firewall restrictions. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Not much changes, just exlude the A from the assorted columns.

The formula in post 3 applies to S5:W5, minor adjustments to L5:Q5 and works just fine.

Oh, I was attempting to preserve your 001 format. Text(reference,"000") changes 1, 2, 5 into 001, 002, 005 by preserving the leading zeroes.

As for the other formula:

=INDIRECT(CHOOSE(COLUMN(A1),"D","F","G","H","E")&3*ROW(A2)-1)


INDIRECT works by taking a string, and evaluating it as a reference

=A1 is a fairly obvious reference, but =A&100-1 doesn't really mean anything to Excel. Instead we use INDIRECT("A" & 100 - 99) which becomes INDIRECT(A1) and then Excel looks at string between the parentheses and evaluates whatever is there as a reference to a cell.

The trick here is to come up with an expression that increments how you need. Your first set starts at 5 and goes by 3. I went with 3 x 2 - 1.

CHOOSE takes the first arugment, and chooses that of the next N paramaters, such that CHOOSE(1,1,2,3) = 1, CHOOSE(2,1,2,3) = 2, CHOOSE(3,1,2,3) = 3

COLUMN(A1) is a shortcut for writing the number 1, such that this number will increment as the formula is copied across columns. This formula in a given cell copied one cell to the right become COLUMN(B1) = 2, further right again COLUMN(C1) = 3, and so on. So instead of writing 1, 2, 3, etc formula designers will use row() and column() when copying formulas up and down and left to right.


Let's evaluate this formula from the inside out to watch it work. We can do this by highlighting sections of it and hitting F9.

=INDIRECT(CHOOSE(COLUMN(A1),"A","D","F","G","H","E")&3*ROW(A2))
=INDIRECT(CHOOSE({1},"D","F","G","H","E")&3*ROW(A2)-1)
=INDIRECT({"D"}&3*ROW(A2)-1)
=INDIRECT({"D"}&3*{2}-1)
=INDIRECT({"D"}&{5})
=INDIRECT({"D5"})
=D5


We copy this formula down 5 rows, and over 4 columns and see its reference change:
=INDIRECT(CHOOSE(COLUMN(E6),"D","F","G","H","E")&3*ROW(E7)-1)
=INDIRECT(CHOOSE({5},"D","F","G","H","E")&3*ROW(E7)-1)
=INDIRECT({"E"}&3*ROW(E7)-1)
=INDIRECT({"E"}&3*{7}-1)
=INDIRECT({"E"}&{20})
=INDIRECT({"E20"})
=E20