Is there an easier way to code this copy?

Glenn

Active Member
Hi,

Hope there is an easier way!

I have formulas in column E as below and I have simplified them. They reference the data in columns A, B and C.

I have many more than this an I was wondering if there is a way to code this so that when I copy cell E1 down, it picks up the correct cells in columns A, B and C?

Thanks, Glenn
PSizing.xls
ABCDE
110203010
240506020
330
440
550
660
Sheet1

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

RAM

Well-known Member
If the formulas in E2 below E1 is the same formula as E1, but in reference to A2, B2 and C2 then select E1 and place the curser in the right bottom corner of that cell (where the curser changes to a cross), left-click and drag it down to the last cell. The cells below will update accordingly.

Make sure you haven't anchored any cells, i.e. \$A\$1.

HTH

RAM

Edit: PS. Use the View Source button and copy and paste code in Mr. Excel. You will avoid all, [] space-signs

Thanks Ram.

Cheers, Glenn

Andrew Poulsom

MrExcel MVP
Try:

=OFFSET(A\$1,(ROW()-ROW(A\$1))/3,MOD(ROW()-ROW(A\$1),3))

in E1 copied down.

mdavidge

Board Regular

Well,

It is a bit of a way to go, but it works. Auto fill F column sequence A, B, C. Formula G start on row 4 with =G2+1. Formula H is Concatenate F&G Values. Formula E is Indirect reference corresponding G cell reference. They all auto fill down.

Mike
Book1
ABCDEFGHI
1INDIRECTREFREFCONCATENATE
21020300A1A1
34050600B1B1
47080900C1C1
510011012010A2A2
613014015020B2B2
716017018030C2C2
819020021040A3A3
922023024050B3B3
1060C3C3
1170A4A4
1280B4B4
1390C4C4
14100A5A5
15110B5B5
16120C5C5
Sheet1

SydneyGeek

MrExcel MVP
Hi Glenn, try this in E1, filled down:

=OFFSET(D1,FLOOR(ROW()+2,3)/3-ROW(),MOD(ROW(),3)-4+(MOD(ROW(),3)=0)*3)

Denis

mdavidge

Board Regular

Andrew,

Your solution works perfectly. I do not understand how the ROW() and -ROW() work inside of the OFFSET and MOD calculations. I see this technique with many formulas. Is there a quick explanation?

Mike

=OFFSET(A\$1,(ROW()-ROW(A\$1))/3,MOD(ROW()-ROW(A\$1),3))

Andrew Poulsom

MrExcel MVP
The second and third arguments for OFFSET are number of rows and number of columns respectively.

Number of Rows

You want rows 1 to 3 to refer to row 1, rows 4 to 6 to refer to row 2, etc. Deducting 1 (ROW(A\$1)) from the current row gives 0,1,2,3,4,5 and dividing by 3 gives 0,0.33,0.67,1,1.33,1.67. Offset takes only the integer portion, being 0,0,0,1,1,1.

Number of Columns

You want rows 1 and 4 to refer to column 1, rows 2 and 5 to refer to column 2, and rows 3 and 6 to refer to column 3. Deducting 1 from the current row and dividing by 3 gives a remainder (MOD) of 0,1,2,0,1,2.

RAM

Well-known Member
I didn't read up correctly on the problem and gave a totally wrong answer. Thank you to everybody else who did a better job than I did.

RAM

mdavidge

Board Regular
Andrew,

Thank you so much for the explanation. This whole ROW() concept has finally sunk into my thick head. It will come in very handy.

Thanks Again,
Mike

Replies
2
Views
134
Replies
5
Views
214
Replies
5
Views
369
Replies
2
Views
71
Replies
7
Views
66