Sort Named Range
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($B15 5,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 |
Sort Named Range
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. "Pam" wrote: 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($B15 5,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 |
All times are GMT +1. The time now is 01:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com