Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function like INDIRECT to return a range?
Hello. I'm using an array formula that will only accept a finite range as a
parameter (e.g. A2:A999). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data. Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?). Any ideas? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function like INDIRECT to return a range?
Maybe you can use a range that grows and contracts depending on the entries:
http://contextures.com/xlNames01.html#Dynamic (from Debra Dalgleish's site) Wilba wrote: Hello. I'm using an array formula that will only accept a finite range as a parameter (e.g. A2:A999). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data. Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?). Any ideas? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function like INDIRECT to return a range?
Hi,
If your data are contiguous in the column(s) referenced in the array formula, the following method may work. Supposing your data range is in A2:A999: In an empty cell (say P2) enter the formula, ="A"&COUNT(A:A)+1 (The '1' at the end corresponds to one less than the starting row number of your data range. You have to modify that number if the range starts at some other row). Now P2 will show the cell reference for the last row (in this example, A999) In your array formula enter A2:INDIRECT(P2) to mimic the actual range, i.e., A2:A999; however, the formula will be dynamic and if more rows are added to column A, the INDIRECT function will take care of it. I hope this works! Best regards, B. R. Ramachandran "Wilba" wrote: Hello. I'm using an array formula that will only accept a finite range as a parameter (e.g. A2:A999). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data. Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?). Any ideas? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function like INDIRECT to return a range?
Dave Peterson wrote:
Maybe you can use a range that grows and contracts depending on the entries: http://contextures.com/xlNames01.html#Dynamic (from Debra Dalgleish's site) That's cute, I hadn't seen that before. I got what I want by using MATCH in the OFFSET formula to find the first and last occurrences of a start date and end date, then I used the dynamic range in the array formula. Superb, thanks a bunch! Wilba wrote: Hello. I'm using an array formula that will only accept a finite range as a parameter (e.g. A2:A999). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data. Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?). Any ideas? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function like INDIRECT to return a range?
B. R.Ramachandran wrote:
If your data are contiguous in the column(s) referenced in the array formula, the following method may work. Supposing your data range is in A2:A999: In an empty cell (say P2) enter the formula, ="A"&COUNT(A:A)+1 (The '1' at the end corresponds to one less than the starting row number of your data range. You have to modify that number if the range starts at some other row). Now P2 will show the cell reference for the last row (in this example, A999) In your array formula enter A2:INDIRECT(P2) to mimic the actual range, i.e., A2:A999; however, the formula will be dynamic and if more rows are added to column A, the INDIRECT function will take care of it. I hope this works! It would work for the way you have interpretted the question, but I didn't make it clear what I meant - sorry, my fault. A2:A999 is not the range I want, it's just an example of a the kind of finite range that the array formula will accept. The actual range I want is a subset from a column of dates in ascending order, which includes everything from the first occurence of an arbitrary start date to the last occurence of an arbitrary end date. Dave Peterson's solution works brilliantly for what I want. Thanks very much for taking the time to help me! "Wilba" wrote: Hello. I'm using an array formula that will only accept a finite range as a parameter (e.g. A2:A999). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data. Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?). Any ideas? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
can I use Indirect function within range definitons for names? | Excel Worksheet Functions | |||
I would like a function to return a value that fits its range | Excel Worksheet Functions | |||
Can INDIRECT return a range array? | Excel Worksheet Functions | |||
How to paste INDIRECT function to range of cells? | Excel Worksheet Functions |