Cell Reference in Excel

Is it possible to maintain a cell reference when you format the relevant cells in a table, and then sort the contents from smallest to largest? In this case, devices eg. cell A1 (Sheet No. 1): "example 1". Cell A1 in (sheet No. 2) to reproduce exactly the same as A1 (Sheet No. 1), and I therefore use the following formula: = 'Sheet1'! A1. How will the subsequent cells also reproduced in (A2, A3, A4 ... etc). When I format the table and sort cell contents from smallest to largest, change the cell reference itself. Is it possible to maintain this reference, despite the sort? Have tried absolute cell reference with no luck..

My Excel 2007 behave quite the opposite, ie that you obviously want. But you say nothing about the version.

An alternative and more consistent solution you can try:
=INDIRECT("Sheet1!A1")

Thanks for the quick response. I am too on Excel 2007. Tried INDIRECT formula, but got the same result. Some more ideas?

Well then, I misunderstood you. "Changes the cell reference to" means "does not change", A2 will continue to be A2, you want the cell to be to point to Example 1 no matter where it resides in the list?

No, it doesn't like that.

But this rises in real life? What do you are trying to do? Is there an item number? Are you going to make a summary, of what in this case?

Yes, the cell must point to 1 as no matter where it resides. It originates in a excel sheet that combines a shopping list of recipes. Goods that are in the shopping list should be rendered in the appropriate recipes. So if I change the description of an item in your shopping list, it will also change in the pattern. I will also be able to enter new products continuously in the shopping list, and then sort it alphabetically.