Change range in formula based on changing row count

**razz0807** · 04-17-2012, 04:32 PM

Okay.... I'm stumped and am likely making this more complicated than it needs to be... so here I am.

I have a bunch of formulas that are working great. The issue is that the number of rows changes frequently as data is added or removed from the source sheet ("Sheet X"). I'm trying to avoid selecting an enormous number of rows "to be safe" and have run into issues with the row range I used shrinking when data is deleted.

I would like to "calculate" the range so that it automatically changes when the number of rows changes. Is there a way that I can recalculate the range (within the formulas) without using VBA?

Example formula here:
=SUMPRODUCT((YEAR('Sheet X'!R2:R200)=YEAR(TODAY())-1)*('Sheet X'!B2:B200="Implemented"))

I'd like to be able to have "200" change to match the number of rows in use (using a formula).

Thanks in advance.
Razz

**ron2k_1** · 04-17-2012, 04:48 PM

The formula is evaluating data tables that are available on the same workbook. If you Insert rows, the range will update automatically. I guess you can you some sort of INDIRECT to accommodate the array formula =MAX(ROW(A1:A500)*(A1:A500<>"")) which will give the last used row, but it will be very slow and memory hungry.

I suggest that you just do a find/replace business (Ctrl + H) and look for the row number you have on your formula and then replace it with whatever you have. I, personally, would include at least 10% more rows than you actually need just to be safe.

**razz0807** · 04-18-2012, 09:49 AM

Tried this approach for a while and it was becoming a real pain to maintain which is why I was looking for a better solution. (Multiple people were updating the source data and not always inserting to maintain the range).

**Cutter** · 04-17-2012, 04:56 PM

You can use Dynamic Named Ranges.

See link: http://www.contextures.com/xlnames01.html

**ron2k_1** · 04-17-2012, 05:04 PM

Yep, dynamic named ranges will do it, here is one I was looking at right:
http://www.excelforum.com/excel-gene...n-formula.html

**Palmetto** · 04-17-2012, 08:33 PM

In Excel 2007+, convert the data source to an Excel Table (Ribbon Menu > Insert > Table)

Make sure your table has headers.
Excel automatically treats tables as dynamic ranges and you can use the table headers in the formulas to reference the entire table/column - no need to create dynamic named ranges.

So you might end up with a formula similar to:

=SUMPRODUCT((YEAR(Table1[Date])=YEAR(TODAY())-1)*(Table1[Status]="Implemented"))

Where Date and Status are the header names in the table.

**razz0807** · 04-18-2012, 09:51 AM

Thanks so much everyone. The dynamic range idea is working beautifully. I didn't even know I could do that. I love it when I learn something new. You saved me a lot of work & may I say... you rock!

Thanks Again.
Razz