Sorting when formulas reference a different sheet

Hi,

I have a spreadsheet where student data is entered. When scores are entered into Column C & F then G calculates the growth. This is done by referencing a cell in another sheet that is hidden ='June M'!AB43. There are similar formulas in columns K & M.

My problem is that when a new student arrives the data needs to be sorted alphabetically by column A (Family Name). When this happens the formulas move with the rest of the data and then the results aren't calculated correctly. I need the formulas to stay in the same spot. (I've tried adding $$ but this doesn't work).

I've used a macro to make it easier for users to sort with a click of a button (I've had issues with people inserting rows and mucking up formulas). The only way I have been able to get this to work is by the macro copying & pasting the columns with formulas further down the sheet, sorting the data in the table and then copying the formulas back up to the top. This seems to work.

I was wondering if there is a better way of sorting the data while keeping the formulas as they are in their current cells. I would prefer this as there may need to be columns added in or deleted. If the formulas stay in their current cells I could extend the sort range to include additional columns and I wouldn't need to alter the macro every time a column is added/deleted.

I've attached the spreadsheet to make it easier. I hope that makes sense.

Try this,
In G6, then drag down

Please Login or Register  to view this content.

Similarly Apply for other formulas also.

Thanks. That was the simple solution I was looking for. I did read another post about Indirect formulas but didn't really understand them. Thanks again. Much appreciated.

Thanks for feedback.