Sorting text underneath numbers always

Hello.

I've been looking for a solution to my problem everywhere and hope someone can help me.

I have a complex 'status' cell on a spreadsheet that for each row produces a score for each client based on how well the relationship is going. I want to be able to sort the column easily so that I can see best or worst scores easily at the top for prioritisation for the team etc. Easy peasy, so far.

However, I also need a lot of empty rows at the bottom so the team can easily add more clients to the list without being able to accidentally edit the formula. So I've copied the 'status' cells all the way down and then locked the column. Problem is now, that the empty cells calculate 0, and so if you ever sort the row Z-A to see worst scores at the top, then all the empty cells go top and all the real data is way down off screen. I've tried putting an extra 'if' on the front to detect if any data is present in the row, and post a blank if needed - but excel reads that as 0, not null, so problem isn't solved.

My idea at the moment is to have blank rows post a text string. But here's my question: is there a way to have the sort always put numbers before text, so that the text is always dumped to the bottom, regardless of whether you sort biggest to smallest or smallest to biggest? I hope that makes sense.

Alternatively, if anyone has a better solution, I'm all ears!

Thank you!

Example problem.xlsx

Sorry! Hopefully that works

Hmm. Thanks. I'd not thought of a table.

One problem with this though, is that the furthest left column (plan), doesn't add the drop-down list to new rows as they are created. Is there a workaround for that?

Thanks, Glenn.

I see what you've done works, and I can see you've put =list. But I'm not sure how to recreate it. How do I select the cells? I put =list in the data validation box and get "A Named range you specified cannot be found"