"kuansheng" wrote:
.. using formula that refers to data data on the BOM worksheet
this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP,
='BOM'!C2 in A6 of MP and so on.
And i repeat by copying the formulas to cell
below to extract the data for row 2 of the BOM worksheet.
I continue until i get all 100 rows.
My problem is when i copy this formula to some cell below,
it does not extract the next row of data from the BOM worksheet.
Instead it make relative reference to the cell thus returning wrong data.

Perhaps try this:

In A5: =INDIRECT("'BOM'!A"&INT((ROWS($A$1:A1)-1)/2)+2)
In B5: =INDIRECT("'BOM'!B"&INT((ROWS($A$1:A1)-1)/2)+2)
In A6: =INDIRECT("'BOM'!C"&INT((ROWS($A$1:A1)-1)/2)+2)
In B6: =INDIRECT("'BOM'!D"&INT((ROWS($A$1:A1)-1)/2)+2)

(The above returns the same as the 4 link formulas mentioned in your post
snippet above. The 4 formulas are identical, except for the reference col
letters: A, B, C, D for the sheetname: 'BOM'!)

Select A5:B6, then drag the fill handle at the bottom right corner of B6
down as far as required** to produce the required links to BOM

The above will increment the links sequentially, viz.:

In A5:B6, links point to row2
In A7:B8, links point to row3
In A9:B10, links point to row4

(assume the above incrementation is what you wanted)

**since you want to get 100 rows from BOM, copy down 200 rows to B206
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---