# Thread: explain a formula? (all)

1. ## explain a formula? (all)

I stole from somewhere a formula of the type
=OFFSET(data!\$A\$4,0,0,COUNTA(data!\$A:\$A),1)
for creating a dynamic named range.
I think I can understand how it works, except for the final '1'.
Thanks.

2. ## Re: explain a formula? (all)

The OFFSET function has 5 arguments: cell reference, row offset. column offset, height, width. In your formula:

cell reference = data!\$A\$4.
row offset = 0.
column offset = 0 i.e. the range is not offset from A4.
height = COUNTA(data!\$A:\$A) i.e. the height of the named range is the number of non-blank cells in column A.
width = 1 i.e. the named range consists of one column.

3. ## Re: explain a formula? (all)

Got it!
Thanks, Hans.
So a named range of volatile length and fields, say, name1 and name2 would be 2 columns wide, so the formula would end in '2'?

4. ## Re: explain a formula? (all)

That's correct! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

If you need the width to be dynamic too, you could use COUNTA(data!\$4:\$4) as last argument, assuming that you want to use row 4 to determine the width.

5. ## Re: explain a formula? (all)

Cool.

#### Posting Permissions

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