UserForm Range

I am trying to work my way through some vb with excel userform tutorials and i have came across an example that uses a range to populate the userform, i was wondering if anyone would be kind enough to break this line down for me, i have got myself into abit of a muddle:

=OFFSET(Data!$A$1,1,MATCH("Age",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)


Many thanks

Hi there,

As you say in your post, this formula defines a worksheet range.

The details are as follows:

start from Cell A1 on the "Data" worksheet,
scan across Row 1 of this worksheet until a cell containing the text value "Age" is located,
scan down Column A and count how many cells in that column contain values,
subtract 1 from the above value (probably to exclude the header row), - let's refer to this value as "x",
finally, take the "x" cells below the cell containing "Age", and this is the range you're looking for.

To get a quick indication of how it works, use:

Please Login or Register  to view this content.

to calculate the sum of all of the ages in the range.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again, fellow Irishman!

Many thanks for your feedback - I'm glad I was able to help.

Regards,

Greg M