+ Reply to Thread
Results 1 to 5 of 5

Dynamic Range with user input

  1. #1
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Dynamic Range with user input

    I have a list of months in excel with the MMM-YY format.

    I'm wanting to create a dynamic range that's range is only that of the month the user inputs plus the previous 3 months (so 4 months all together).

    I've attached a sheet showing my list of dates along with the user.

    Any help would be great
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Dynamic Range with user input

    Here is one way to do it - see attached.

    Also added logic to show a blank cell if the user input is first on the list (try user input for JAN2011, FEB2011, MAR2011, APR2011; you'll see the pink range populate).

    This will only work if your input is the first of the month (or use the MAR2011 style of input displayed above). I could enhance it to match any date if necessary.

    Good luck!

    ~Coleman
    Attached Files Attached Files

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Dynamic Range with user input

    Without knowing where this range will be used I'd be inclined to use INDEX rather than the OFFSET - the latter is more succinct but volatile

    Please Login or Register  to view this content.
    I'd also modify D1 to be a validation list sourced from A1:A19

  4. #4
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Dynamic Range with user input

    The range will eventually be used as a graphs x-axis if that changes anything?

    I did get something almost to work with using OFFSET but it didn't like when the top one on the list was chosen as it was trying to offset up and there was nowhere to go

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Dynamic Range with user input

    Note the use of MAX in my prior example... the same principle can be applied to OFFSET

+ 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