Use cross references in Excel

The revenue tabs on my spreadsheet make reference to stock on another tab.

At present I insert = formula in the revenue sheet and click across to the stock sheet to link to the relevant stock item.

For example, on the 'Rev 1' tab, cell C31 references A:23 of the 'Stock' tab.

On the above Rev 1 tab, cells D:31 and E:31 take their values from 'Stock' B:23 and C:23 because of the above assoiation.

Required:-

1) In C:31 of the Rev tab insert a drop down list that references column A of the 'Stock' tab.

2) When a product is chosen from the above list, cause cells D:31 and E:31 to populate with the cost and duty values from B:23 and C:23 in the Stock tab.

3) Extend the above method to rows 76 and 122 of the 'Rev 1' tab so that I can choose any product from the list there and populate the cost and duty values accordingly.

I will then see how you have done this and I will extend the logic to the other rows and tabs in my spreadsheet.