[Solved] Selecting variable range for formulas

    I have a macro that imports data from a text file into a worksheet. The data consists of a set number of columns and a variable number of rows. On another worksheet page in the same workbook, I want to put in formulas to summarize data (sum, average,count, etc) from this worksheet. Since the number of rows can vary from one imported dataset to the next, can anybody tell me if there is a formula that could be used? Or do I need a macro (I hope not). Thanks for any advice! :)

    You can accomplish this using dynamic named ranges....


    Lets say some sample data in column A (but you don't know how many rows).


    goto Insert|Names|Define from toolbar
    Type in a name - let's cal it Dazed


    in the refers to box type
    =OFFSET($A$1,0,0,COUNT($A:$A),1)


    now, in a separate cells do the following


    =SUM(Dazed) gives you the sum
    =COUNT(Dazed) gives you a count
    =AVERAGE(Dazed) gives you the average


    All will work and expand & contract for variable rows of NUmerical data...


    For more help on named ranges, see the Excel page on this site under Dynamic Ranges...


    hth :beergrin:

    The only thing you'll need to bear in mind is that Excel often gets lost looking for values in another sheet where the named range is dynamic.


    To get round this I recommend code something like this to define the named range:


    Private Sub Workbook_BeforeSave _
    (ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim SheetName As String, NameAddress As String



    With Sheet1 ' CodeName
    'Pass Sheet1 Tab name and range A1 currentregion address.
    SheetName = "=" & .Name & "!"
    NameAddress = .Range("A1").CurrentRegion.Address
    'Add the name MyRange
    ActiveWorkbook.Names.Add _
    Name:="Data", RefersTo:=SheetName & NameAddress
    End With
    End Sub

    Robert Hind
    Perth - Western Australia

    Another question along the same line - Lets say the range that I have named contains numbers, like 10, 20, 30, 40, 50. I want to do a formula to count the number of 10's, for example. I tried to do a "=if(dazed='10',count(dazed),0)" thinking it would tell me how many were 10, but it returns a 0. I am going about this wrong? Again, thank you for your advice! :)

    Quote

    Originally posted by r_hind
    The only thing you'll need to bear in mind is that Excel often gets lost looking for values in another sheet where the named range is dynamic.


    G'Day Robert,


    can you elaborate on this ?


    (I was doing my usual referencing on a dynamic range from another sheet this week and I swear to God the ROW formula changed each time I looked at it in insert/names/define..... it was driving me nuts)


    cheers
    Chris

    "I was doing my usual referencing on a dynamic range from another sheet this week and I swear to God the ROW formula changed each time I looked at it in insert/names/define..... it was driving me nuts"


    Chris,


    You more-or-less always need to use absolute references in named formulas - is that what you mean?


    paddy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login