Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Dynamic Ranges /Sorting (XL2003)

    I know this subject has been visited before but I could not find anything using SEARCH that that proved immediately helpful...

    I have a data table where the data actually starts in <font color=blue>B6</font color=blue> . It stretches across to the R column. Total 17 columns. The number of rows grows as new entries are made. I want to place two sort macro buttons at the tops of columns B and C. If it matters: Everytime a new entry is made into the table, there will be a date placed in B and a location (text) typed into C. The other columns D:R will have one entry amongst them.

    I know this is a case where I must make use of a dynamic range. Googling around, I found this snipet for the "refers to:" field under Define Name.
    <pre>=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A ),1)</pre>


    Would changing it to =OFFSET(MySheet!$B$6,0,0,COUNTA(MySheet!$B:$[img]/forums/images/smilies/cool.gif[/img],17) do the right thing? My main concern is the <font color=blue>COUNTA(MySheet!$B:[img]/forums/images/smilies/cool.gif[/img]</font color=blue> portion. Since the data actually begins in B6, it seems that the formula as written would count EVERYTHING in column B (including non-data entries in B1:B4 and the header in B5).
    - Ricky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Ranges /Sorting (XL2003)

    If B1:B5 will always be populated, you can use COUNTA(MySheet!$B:$[img]/forums/images/smilies/cool.gif[/img]-5
    Otherwise, you can use COUNTA(MySheet!$B$6:$B$65536)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges /Sorting (XL2003)

    That statement probably should be:

    <pre>=OFFSET(MySheet!$B$6,0,0,COUNTA(MySheet!$B6:$ B65536),17)
    </pre>


    Depending on what is in B1:B5, your statement might offset too far.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Ranges /Sorting (XL2003)

    Legare / Hans -

    B1:B5 contains some blank cells along with the data header and an overall report header. So, I will type in the statement just as you both have suggested... (B6:B65536). I appreciate the quick responses.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Ranges /Sorting (XL2003)

    After a little trial & error, another change was required before the sort macros would work properly... I had to add absolute cell referencing into the COUNTA function. In the end, this is what was placed into Insert |Name |Define |Refers To:
    <pre>=OFFSET(MySheet!$B$6,0,0,COUNTA(MySheet!$B$6: $B$65536),17)</pre>


    Then adding this code to a couple of macro buttons made it possible to sort the datatable using the B or C column...

    <pre>Sub Button2_Click_SortbyDate()

    Range("dtable").Select
    Selection.Sort key1:=Range("B6"), order1:=xlAscending, key2:=Range("C6") _
    , order2:=xlAscending, header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal
    Range("a1").Select

    End Sub
    __________________________________________________ ___________


    Sub Button3_Click_SortbyPaidTo()

    Range("dtable").Select
    Selection.Sort key1:=Range("C6"), order1:=xlAscending, key2:=Range("B6") _
    , order2:=xlAscending, header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal
    Range("a1").Select

    End Sub</pre>

    - Ricky

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges /Sorting (XL2003)

    Depending whether the columns houses text or numeric,

    =$B$6:INDEX($B:$B,MATCH(REPT("z",255),$B:$[img]/forums/images/smilies/cool.gif[/img]) for text

    =$B$6:INDEX($B:$B,MATCH(9.99999999999999E+307,$B:$[img]/forums/images/smilies/cool.gif[/img]) for numeric

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •