Define named range where user can define size

**nahousto** · 07-02-2009, 04:07 PM

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.

**Chance2** · 07-02-2009, 04:53 PM

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")

**nahousto** · 07-06-2009, 01:56 PM

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?

**DonkeyOte** · 07-06-2009, 02:20 PM

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 ?