Transpose/reference a lot of data easy quick way???

[dracron]

right now I have in column A =worksheet2!$A$2 and column B =Worksheet2!$B$1 for the first 41 cells and then the next 41 cells I have =worksheet2!$A$2 and column B =Worksheet2!$C$1. and so on. Problem is I would have to type this over and over again for 100s of different cells.

Is there a quicker way I can do this?

 

[deanst]

Hi - this is possible using offset, a count function and a mod function, but I can't quite follow the pattern you described above.

Can you please elaborate more on the pattern. In your example above the formula in column A doesn't change as you go down the page? (A =worksheet2!$A$2 )

 

[s hal]

this was reposted and looks to perhaps have been solved. in the second thread.

http://www.mrexcel.com/forum/excel-questions/751028-copy-reference-lot-data-easy-quick-way.html