Sort Value with Reference

**sthorson** · 06-14-2017, 01:15 PM

Hi.
I have a list of Names (A3 thru A14) and individual counts (B3 thru B14) for each of these names.I would like to be able to have a list next to it (in D3 thru D14 and E3 thru E14) that reference the counts, but sort by the values, as the original list in columns A and B are sorted alphabetically.

Currently, in E3 I have =B3 and so on down the column. I then would like to be able to sort the D/E section by the values in the E columns. Is there a way to do this? Currently, when I use the "sort by value" function it just changes around the D column list, but not the values in the E columns - those stay as they are a reference to the B column.

Any thoughts would be greatly appreciated. I know with this small list it is easy to do it manually, but this will be a monthly thing with more rows being added so it would be ideal if there was a way to hit the sort button and have it do it.

Thanks!

**Aardigspook** · 06-14-2017, 03:00 PM

Welcome to the forum.

There are a number of ways you can do this, but here's one of the simplest. It uses a helper column which you can put anywhere - for this explanation, I'm going to use column H.

Leave columns A and B as they are.
In the helper column H, put this in H3 and drag down:

Formula:

Please Login or Register  to view this content.

Now put these in D3 and E3, and drag down:

Formula:

Please Login or Register  to view this content.

The helper column ranks the counts in col B, then the formulae in D and E return the highest ranked name and count, next highest ranked, etc. The ROW()-2 part is because you're starting in row 3, so ROW()-2 gives 1, then 2, then 3, etc.
You can hide column H if you want - the formulae in D and E will still work.

If you change a number in column B, columns D and E will auto-sort.

The formulae above go down to row 14, but you can just replace all the $14 references with $100 so you don't need to change them every time you add another month (or $1000 or $10000 if you want to future-proof it for longer). If you then drag down the formulae in H, D and E, you'll see errors appearing. You can hide these using IFERROR:

Formula:

Please Login or Register  to view this content.

The "" gives blanks instead of errors - you could use "no data" or something like that if you prefer to see how far down you've dragged the formula.

Attached is a file showing this working.
Hope that helps.