Linking formula in subsequent rows to first row info

[lmsmith]

I'm working on a draw/scoring program and have the "WHITE" teams above the "BLACK" teams with a LOOKUP formula to determine points allocation based on scores.

However, if I sort using the auto filter ability, the formulas still try to look in specific cells, rather than looking for the information that's linked to the information in the first cell.

What I'm wanting to be able to do is sort the information into groups "JG", JO", "SG", "SO" and have the "POINTS" value automatically moved when I change the filter.

The info that I'm looking at is sheet "Test1" on attached doc.

[Rebuild8]

Hi lmsmith,
Whenever I use the filter, it preserves the scores. I checked all the "JG" teams and then saved the file. Please check the file I uploaded (also check the Overview sheet too).

[lmsmith]

Hi lmsmith,
Whenever I use the filter, it preserves the scores. I checked all the "JG" teams and then saved the file. Please check the file I uploaded (also check the Overview sheet too).

Maybe it's my version of excel - as soon as I changed the filter (from say, "JG" to "SO", it comes up with #REF! in column D

[Paul]

If you use the Sort function within the filter, yes, you're going to get REF errors. Not sure you're going to be able to sort the list like you're hoping. You could just filter for the JG teams, which is probably what Rebuild was trying. (Click the drop-down button, un-check Select All and then just select the JG teams.. repeat for each subset of teams to show them individually).