+ Reply to Thread
Results 1 to 5 of 5

Dynamic Named Range Picking up Cells with Zeros (0s)

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Dynamic Named Range Picking up Cells with Zeros (0s)

    Have a dynamic named range formula that is picking up zeros in the data range instead of ignoring the zeros and only select values greater than zero. See Formula below. it will require some tweaking to fix the problem. Sample file attached.

    =OFFSET(Chartdata_linked!$C$4,COUNTA(Chartdata_linked!$C$4:$C$613)-Chartdata_linked!$B$4,,Chartdata_linked!$B$4)

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dynamic Named Range Picking up Cells with Zeros (0s)

    I *think* you want your DNR defined by this formula:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Dynamic Named Range Picking up Cells with Zeros (0s)

    Ron Coderre: The formula supplied is now selecting the cells in reserve order instead of only selecting down based on values greater than zero.

    Thanks

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dynamic Named Range Picking up Cells with Zeros (0s)

    With all the gyrations in your original formula, I wasn't sure what range you wanted to select.
    This formula will reference a range that begins at C4 and is the number of cells >0 high
    Please Login or Register  to view this content.
    In your example, that would be C4:C10

    Does that help?

  5. #5
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Dynamic Named Range Picking up Cells with Zeros (0s)

    Ron Coderre: Sorry for the confusion. In the original formula and sample workbook, when you enter 5 in C5, the original formula will select C9:C13 with C11:13 with zero values. Instead, would like the formula adjusted to only select C9:C10 since the cells are not zeros (or greater than zero).

    Thanks

+ 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. Dynamic / Named Print Range To Include Another Range of Cells
    By thekrakenwakes in forum Excel General
    Replies: 0
    Last Post: 07-25-2014, 07:52 AM
  2. Dynamic Named Range and Pivot Tables - Blank Cells
    By nathanB in forum Excel General
    Replies: 1
    Last Post: 11-29-2012, 09:11 AM
  3. Dynamic Named Range on Visible cells
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2011, 02:42 PM
  4. picking cell from named range passed to vba
    By toddah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2011, 11:49 PM
  5. [SOLVED] Dynamic Named Range with blank cells
    By tjtjjtjt in forum Excel General
    Replies: 3
    Last Post: 10-05-2005, 04:05 PM

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