Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named range/cell, sort & delete | Excel Discussion (Misc queries) | |||
Sort a named range | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Named range not expanding with insertions after sort?? | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming |