Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to automatically fill / clone / copy column E (from cell E8 down to E100) to L8, S8, Z8 etc.

Is this possible using a formula?

share|improve this question
1  
Formulae or values / are any cells already occupied in columns to the right of E / are other cells in E (not 8:100) occupied / also, are you sure you need this rather than, say, splitting the pane at E or using E for print titles? –  pnuts Nov 2 '12 at 14:23

3 Answers 3

If you're ok with dragging over all existing columns you can use this formula in F8 and drag across and down:

=if(MOD(column()-5,7)=0,$e8,"")

share|improve this answer

One way would be to put =IF(MOD(COLUMN()-5,7)=0,$E8,"") in L8 and copy down to L100 and then that block across as far as required.

Potentially that is a lot of formulae and these may interfere with subsequent entries such as into M50, so I suggest copying the entire range containing the formulae and then Paste Special, Values over the top.

Counting from 1, ColumnL happens to be 7 to the right of ColumnE but also Column 12 to Excel. So in ColumnL COLUMN() [ie 12] -5 is 7. MOD(…,7) will give a residual for any number not divisible by seven but not (ie =0) when that number is a multiple of seven. The IF function then takes a copy of E8 etc where required and inserts a blank (“”) where not.

share|improve this answer

You can clone the column by entering =IF($E8="","",$E8) in L8, S8, and Z8 and then filling each formula down the column accordingly.

share|improve this answer
1  
That little 'etc.' could be nuisance though it if means a large part of 2,048 times! –  pnuts Nov 2 '12 at 15:55
    
Correction - 2,340 times (slight problem with zero and 1 base!) –  pnuts Nov 2 '12 at 16:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.