Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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'.
    A clue, please?
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: explain a formula? (all)

    Hadn't thought of that!
    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
  •