Sort Named Range

[Pam]

Hi,

I have a portion of my spreadsheet that pulls information from a different
area on spreadsheet and changes when data for specific salesperson is
selected on another spreadsheet. Example below:

=IF(ISNA(MATCH(Dashboard!$F$70,{"BB","BE","BH","DP","DT","GM","GP","MRH","WP"},0)),0,OFFSET($B155,0,-1+1*MATCH(Dashboard!$F$70,{"BB","BE","BH","DP","DT","GM","GP","MRH","WP"},0)))

This works great and covers A173:B187. ColA has the Product Name and ColB
has the value. ColA never changes and ColB values change per salesperson.

If I use the following code (found on internet) to sort section, it will
only work if I manually enter amounts to cells in ColB.

Sub Worksheet_Change(ByVal ObjTarget As Range)
If ObjTarget.Column = 2 Then
Set objRange = Application.Range("b173")
Set objRange2 = ActiveSheet.Range("SortRange")
objRange2.Sort objRange, xlDescending, , , , , , xlNo
End If
End Sub

I would like for it to sort ColB in descending order, as well as ColA to
match value, when the name is selected on the other data sheet and all
values reflect selected name. Is this possible? I've spent a considerable
amount of time and decided it was time to ask the experts!! I hope all this
is clear.

Please know that any help is greatly appreciated.
Thanks in advance,
Pam

 

[Joel]

You can't sort on data that has a formula. You need to convert the formulas
to values using PasteSpecial. If I wrote a macro I would either copy the
table to a new section of the workbook using copy and then pastespecial
values only. Or I would remove the formulas from the current table by
PasteSpecial in the same location of the worksheet which will remove the
formulas.

You can do the same thing witout a macro. if you can't sort the data using
the worksheet sort then you won't bed able to do it with a macro.