Dynamic Range while adding static single row

Hi,

Brand new to the forum, but I've been using your forum for many years working with Excel for out of the box solutions. Happy to finally be a member and am looking for another out of the box solution .

I'm tinkering with an XIRR function and am having a small issue with the dynamic range. To give you an example, I have my dates within C8:C200 and the list moves when the support tables are refreshed. So for a particular fund if I run this there may be date values from C8:C50, but the rest of the range returns #Value! (as it should, because there is no data in those rows from the support tables.) My current dynamic range sets the array from C8: to the last date before the #Value. This works fine, but I need to add one additional static row to the array. In the above example if my dates go from C8:C50, I also need to include the date from cell C201. If it was a sum function it would look like SUM (C8:C50,C201).

The formula I'm using for the dates is:

=OFFSET(Main!$C$8,0,0,COUNTIF(Main!$C$8:$C$200,"<>#Value!"),1)

I'd need the same formula for values, but the formula would use the same syntax.

Any thoughts on this would be appreciated!

Thanks,
B

Instead of putting the formula in row 201, why not put it above row 8, so it will not interfere with the main data and will always be visible if you put Freeze Panes on, rather than scrolling down to see it.

Hope this helps.

Pete

Hi Pete,

Thanks for the quick response! The formula doesn't actually sit in 201, it sits in C204. I'm not having trouble with the scroll, I just need to add a single static row to the dynamic range (on top of the data within C8:C200 that doesn't have a #Value!). The range should be C8:C200 AND C201. The issue is that I have #Values! between rows 51 and 200. If I use an offset for this, it gives me 51 rows and that last row is the first #Value and not row 201. Hopefully you can follow this!

Thanks again,
B