Filling Non Adjacent Cells

[Walt1306]

I have been struggling with this for a while. I am trying to fill a formula throughout a row. The formula is similar to (6*A$1) . However, when I drag it automatically goes to (6*B$1). I want to formula to skip cells. For instance, I want the cell order A1,C1,E1,G1,I1 . Instead I am getting A1,B1,C1,D1,D1. Is it possible to fill non adjacent cells.

 

[Andrew Poulsom]

Try:

=6*OFFSET(A1,0,COLUMNS($A1:A1)-1)

 

[Legacy 11779]

Alternatively (for academic interest only, since only good up to column Z) :-

=6*INDIRECT(CHAR(COLUMN()*2+63) &"1")

 

[Walt1306]

Thank You, but one more question relating to this. How do I apply these functions to the case where I am copying cells from another page. For example

='[TR-OCT02.XLS]LR6-1'!$B$3

 

[Andrew Poulsom]

What is your formula and in what cell?

 

[Walt1306]

Well, in this case there is no formula. I am trying to copy information from one excel file to another excel file. For instance I want to copy information to cell A1 from another cell located at
'[TR-OCT02.XLS]LR6-1'!$B$3 and have that information
go to the sequence b3,d3,f3,h3,j3 etc.

 

[Andrew Poulsom]

Enter this formula in cell A1:

=OFFSET('[TR-OCT02.XLS]LR6-1'!B3,0,COLUMNS($A1:A1)-1)

and copy across as far as is needed.

If you want to break the links, select all the cells containing the formula and choose Edit, Copy then Edit, Paste Special, check Values and click OK.

 

[Walt1306]

Thank You