+ Reply to Thread
Results 1 to 4 of 4

Dynamic Range Formula

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    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:
    =ADDRESS(ROW('Graph Data'!L19),COLUMN('Graph Data'!L19),,,"Graph Data")&":"&ADDRESS(COUNTA('Graph Data'!$L$19:$L$23)+18,COLUMN('Graph Data'!S19))

    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. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    Re: Dynamic Range Formula

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

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dynamic Range Formula

    How are you using it?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] chart based on dynamic range which resizes as per data in range-formula / vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2014, 04:02 PM
  2. [SOLVED] Change Static Range of Formula to Dynamic Range
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2014, 10:15 AM
  3. [SOLVED] Help - Formula To Sum A Dynamic Range
    By keithfender in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2013, 09:34 AM
  4. dynamic range formula
    By niceguy21 in forum Excel General
    Replies: 7
    Last Post: 11-02-2012, 01:52 PM
  5. [SOLVED] Dynamic Range with unused formula messing up x axis on dynamic graph
    By cabybake@yahoo.com in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-02-2006, 04:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1