+ Reply to Thread
Results 1 to 4 of 4

Define a dynamic range from a specific starting point in a list

  1. #1

    Define a dynamic range from a specific starting point in a list

    I am having trouble defining a range name that uses the offset function
    to define a range from a specified starting date within a list of dates
    to the last date in the list. The formula I am working with is as
    follows:

    =OFFSET(First_Date_In_List,MATCH(Starting_Date,Date_List,0)-1,0,Number_of_Days,1)

    This formula defines the range from the specified starting date down
    the specified number of days. I would rather not specify a number of
    days but just define the range to include all subsequent dates. The
    list is dynamic with new data added daily so the last date is always
    increasing. In VB it would just be End(xlDown) but I cannot figure out
    the syntax for a formula.

    Anybody know?


  2. #2
    Bob Phillips
    Guest

    Re: Define a dynamic range from a specific starting point in a list

    How about

    =OFFSET(First_Date_In_List,,,COUNTA(Date_List),1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    <[email protected]> wrote in message
    news:[email protected]...
    > I am having trouble defining a range name that uses the offset function
    > to define a range from a specified starting date within a list of dates
    > to the last date in the list. The formula I am working with is as
    > follows:
    >
    >

    =OFFSET(First_Date_In_List,MATCH(Starting_Date,Date_List,0)-1,0,Number_of_Da
    ys,1)
    >
    > This formula defines the range from the specified starting date down
    > the specified number of days. I would rather not specify a number of
    > days but just define the range to include all subsequent dates. The
    > list is dynamic with new data added daily so the last date is always
    > increasing. In VB it would just be End(xlDown) but I cannot figure out
    > the syntax for a formula.
    >
    > Anybody know?
    >




  3. #3

    Re: Define a dynamic range from a specific starting point in a list

    I believe that simply returns all items in the list. I'm looking to
    return only those items subsequent to the specified starting date which
    is not the first date in the list.


  4. #4
    Dave Peterson
    Guest

    Re: Define a dynamic range from a specific starting point in a list

    How about:

    =OFFSET(First_Date_In_List,MATCH(starting_date,date_list,0)-1,0,
    COUNTA(date_list)-MATCH(starting_date,date_list,0)+1,1)



    [email protected] wrote:
    >
    > I am having trouble defining a range name that uses the offset function
    > to define a range from a specified starting date within a list of dates
    > to the last date in the list. The formula I am working with is as
    > follows:
    >
    > =OFFSET(First_Date_In_List,MATCH(Starting_Date,Date_List,0)-1,0,Number_of_Days,1)
    >
    > This formula defines the range from the specified starting date down
    > the specified number of days. I would rather not specify a number of
    > days but just define the range to include all subsequent dates. The
    > list is dynamic with new data added daily so the last date is always
    > increasing. In VB it would just be End(xlDown) but I cannot figure out
    > the syntax for a formula.
    >
    > Anybody know?


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1