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!
[Solved] Selecting variable range for formulas
 dazed
 Closed


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 InsertNamesDefine from toolbar
Type in a name  let's cal it Dazedin 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 averageAll 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 StringWith 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 
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 
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 moreorless 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!