# [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:

• WillR - THANK YOU!!!! :thumbcoo:

I will try that!

• 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 & "!"
'Add the name MyRange
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!

• Hi dazed,

I think you want the countif function.

=COUNTIF(dazed,"=10")

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• That will do it. Thanks again! I owe you one! :beergrin:

• 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?