Sorting on one sheet messes up my formula from a different sheet

[dmoore702]

I have two sheets for a fantasy golf contest, a money sheet and a scoring sheet. One column from the money sheet references a cell from the scoring sheet, (='Score Sheet'!G3).
When I run a macro to sort the scoring sheet, I want the column from the money sheet to follow the sort. The cell remains (='Score Sheet'!G3) after the sort when I'd like it to be K3 or wherever the sort takes it.
Attached is my scoring sheet. If I sort by session 2 row (largest to smallest) then McBoing would be first, RatFarts second etc, but the formula from the other page does not follow the sort
Any ideas?? Thanks in advance

 

[mikenelena]

Hi dmoore702,

To make the reference in the money sheet column update after the sort, you can use the OFFSET function in the cell formula to dynamically reference the correct cell based on the new position after the sort.

Assuming the column with the reference formula is column G in the money sheet and you want to reference column K in the same row after sorting, you can modify the existing formula to:

=OFFSET('Score Sheet'!$K$1, MATCH('Money Sheet'!A1,'Score Sheet'!$A:$A,0)-1, 0)

Here, the OFFSET function starts at cell K1 on the Score Sheet, and then uses the MATCH function to find the row in column A of the Score Sheet that matches the value in cell A1 of the Money Sheet. The MATCH function returns the relative position of the row in the range, so we subtract 1 to get the correct row index for the OFFSET function. The 0 in the final argument of the OFFSET function indicates that we want to reference the same column (i.e., zero columns over from K).

After you run the sort macro, the formula in the money sheet column will update to reflect the new position of the referenced cell on the Score Sheet.

I hope this helps you. Let me know.