How to offset previously offset-ed cell ?

Hello there - I am a very basic excel user (and by that I mean it). I have a simple spreadsheet done but now I am working on another version of it that will display just the information I want and in different layout. So my first column have all the legend and it just goes from A1 cell to A50, I have then each "product" inserted in the columns to the right so for example it's A2, B2 etc. However in my case each column is made from two columns some of which I have merged. So in fact in my starting cell which is the name of the products its merged from cells b2 and c2. In new sheet I want to populate certain cells automatically so I don't have to manually copy&paste. Because of the merging cells I discovered I need to use offset to get the right data. The further complication is that I am now coping this long row into a column. So so far I came up with the following fx:

for the initial cell in new sheet

Formula:

=OFFSET(Sheet2!B$10,,0)

for the second cell in the same sheet

Formula:

=OFFSET(Sheet2!B$10,,2)

However my problem is the next cell and next after that and so on. It only seems to take the previous cell if I autofill down the column so every time I am getting same value as a result. Which for second formulas is in fact cell D$10 but in third I need to have F$10 and then H$10 and so on. And I just can't get around it unfortunately.

I'm very sorry if this all is explained in a clumsy way but that's best I could come up with...

Originally Posted by nflsales

=OFFSET(Sheet2!B$10,,(ROW($1:1)-1)*2)
TRY THIS AND COPY TOWARDS DOWN

It autofills B10 cell only all the way down.

Originally Posted by nflsales

=OFFSET(Sheet2!$B$1,COLUMN(A:A)-1,(ROW(1:1)-1)*2)
It is not preparable if you attach sample file then we can use index formula

Thank you very much it works, would never have guessed that myself. Thanks again.