+ Reply to Thread
Results 1 to 5 of 5

Define named range where user can define size

  1. #1
    Registered User
    Join Date
    07-02-2009
    Location
    NC, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Define named range where user can define size

    I need to define a named range where the size of the named range is linked back to cell in the spreadsheet. This will let my user define where the range starts and stops by simply inputing values into these cells. I tried using the indirect function to define the range. For example, I entered =INDIRECT("Sheet1!" & "$I$" & A1 & ":$I$23") t define the range, with the hopes that I could then enter a number in cell A1 which would deterime which row the named range starts on. But it didnt work. Any ideas would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Define named range where user can define size

    What exactly are you trying to do?
    INDEX or OFFSET may work better, however this should work (as a NAMED range) as long as A1 is between 1 and 23

    =INDIRECT("Sheet1!$I$" & A1 & ":$I$23")

  3. #3
    Registered User
    Join Date
    07-02-2009
    Location
    NC, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Re: Define named range where user can define size

    I have several columns of data, where each row contains data for a given date. The user need to be able to define a date range, and the model needs to calculate statistics of (mean, stdev, etc...) for that date range. I figured the easiest way to do this was to assign a named range to each column and have the user define the size of the named range to determine which dates they want to run the statistics for. Is there a better way?

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

    Re: Define named range where user can define size

    As Chance2 said INDEX is most likely preferable, ie instead of

    =AVERAGE(INDIRECT("Sheet1!I"&A1&":I23"))

    use

    =AVERAGE(INDEX(Sheet1!I:I,A1):Sheet1!I23)

    INDEX has the advantage over INDIRECT of being non-volatile (and potentially easier to transition to other columns)

    Are you saying in addition to the start row being a variable you want to make the column (I) a variable based on some user selection ?

  5. #5
    Registered User
    Join Date
    07-02-2009
    Location
    NC, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Define named range where user can define size

    Awesome! I think think this will work perfect.

+ 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