Making formula increment when copy/paste

[curiousgeorgeuk]

I have a sheet that i would like to copy and paste a formula across 100 columns, however Sheet 1 (data) has 100, and sheet 2 has 200. The reason for this is Sheet 2 pulls in the data then each Column has an additional column next to this for manual in put or a value.

Sheet 1.

Column
1 2 3 4 5 6 7 8 9 10

Sheet 2
1 A 2 A 3 A 4 A 5 A 6 A 7 A 8 A 9 A 10 A

I want Sheet 2, to pull in the column titles from sheet 1. Is this possible with a formula, Or do i need to do each one individually?

Thanks

 

[Special-K99]

This assumes there is no data in the even numbered columns of Sheet 2, ie where youve put A's

in Sheet2!A1

=IF MOD(COLUMN(),2)<>0,INDEX(Sheet1!A1:ZZ1,1,INT(COLUMN()-1)/2+1,"")
and copy across for 200 columns