Is there an easier way to code this copy?

Glenn

Active Member
Joined
Sep 20, 2003
Messages
325
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
 

Some videos you may like

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
Joined
Oct 4, 2004
Messages
1,862
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
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87

ADVERTISEMENT

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
Joined
Aug 5, 2003
Messages
12,251
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
Joined
Oct 14, 2005
Messages
87

ADVERTISEMENT

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Oct 4, 2004
Messages
1,862
I didn't read up correctly on the problem and gave a totally wrong answer. :oops: Thank you to everybody else who did a better job than I did.

RAM
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,017
Messages
5,569,640
Members
412,284
Latest member
Daibear
Top