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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top