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.