Cell Reference and sorting

[dtp81390]

Ok so here is the situation.

I have a variety of cells on Sheet 1 These are specific programs and the cells that hold the reference are not contiguous, that I would like to have a reference to Sheet 2 which lists all of the programs along with other information about those programs. There is a macro that runs automatically that adjusts values on Sheet 2 and sorts the data.

The issue that i am running into is that if I use =Sheet2!Q9 in the Sheet 1 cell. When Sheet 2 gets sorted the cell reference in sheet 1 remains the same and is now pointing to the wrong value.

Is there anyway to always associate the Sheet 1 cell with Sheet 2 cell with sorting. Is there a workaround for this, I am willing to use VBA to resolve this.

[mikeTRON]

This question is too ambiguous for me to help without a sample workbook.

I would try to not have a hard link to a cell, but instead logic to find the cell, like INDEX or MATCH or something.

[dtp81390]

I have attached a screen shot of the sheets in question, redacted the data that is sensitive. The actual workbook or a sample would be too intensive to post. Hopefully you can use this information.

[mikeTRON]

What is the unique identifier for the COMPLETED yes or no?

You should just use a VLOOKUP, but again a screenshot is not really adequate way for me to help you.
Create a SAMPLE workbook that is representative of the actual workbook and have fake data in it.