# Dynamic Range Formula

1. ## Dynamic Range Formula

Hello,

I have the followingformula that I am using as a named range in order to get it to be dynamic. I can then use the named range for a pivot or graph so that it always picks up the appropriate range as the data changes. In this case I have a table of data from L19 through S23. L19 has the headings in it. The issue I am having is right now the headings in O19 through S19 are currently blank. That may change over time but right now they are blank. The range below is resulting in the following: 'Graph Data'!\$L\$19:\$S\$23. That is the range if all of the headings were there. There is only data in L19 through N23. Any suggestions on how to get the last part of the formula COLUMN('Graph Data'!S19) more dynamic? I need it to recognize that currently the data in O19 through S19 is blank and not pick it up in the range.

Formula:

Current Result:
'Graph Data'!\$L\$19:\$S\$19
Wanted Result:
'Graph Data'!\$L\$19:\$N\$23 (with the functionality to have N change based on data getting populated over time.

Thanks!

Regards,

Anthony

2. ## Re: Dynamic Range Formula

Assuming the headers are TEXT entries and you actually don't need to have the headers as part of the dynamic range:

=L20:INDEX(L20:S23,0,MATCH("zzzzz",L19:S19))

An example:

Data Range
 L M N O P Q R S 18 19 Header1 Header2 Header3 ------ ------ ------ ------ ------ 20 52 69 92 45 17 63 78 82 21 37 89 43 70 82 19 67 31 22 15 36 53 13 69 89 25 50 23 17 66 66 87 67 89 57 61 24

=SUM(L20:INDEX(L20:S23,0,MATCH("zzzzz",L19:S19)))

Returns the sum of L20:N23.

3. ## Re: Dynamic Range Formula

I get a #VALUE when i use the formula you suggested.

4. ## Re: Dynamic Range Formula

How are you using it?

The function(s) you use it in must be able to accept a 2d range.

There are currently 1 users browsing this thread. (0 members and 1 guests)