+ Reply to Thread
Results 1 to 11 of 11

Dynamic name range not working correctly

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Dynamic name range not working correctly

    I have a dynamic name range called Serialnums which references all the rows in column B in the EquipmentData Sheet. However the range is not including all cells as there are 17 rows below containing data in the same column not included in the range, and I can't figure out why. This is my formula:
    =OFFSET(EquipmentData!$B$1,1,0,COUNTA(EquipmentData!$B:$B)-1,1)

    I have also attached my worksheet.
    Attached Files Attached Files

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Dynamic name range not working correctly

    Total 17 cells are blank in the series..

    Try this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Dynamic name range not working correctly

    Or try using the Excel Table feature. Automatic dynamic named ranges for everything. I would use that on all tables. Only thing is if you are using it for datavalidation you need to assign a named range in between cause datavalidation don't read Table names or something.
    In the yellow cell I put in MAX of your serial numbers, just as an example.

    Here is some more reading and links.
    http://www.excelforum.com/excel-form...sheet-2-a.html
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Dynamic name range not working correctly

    Thank you for your responses. Jacc I dont think that would work for me as the dynamic ranges being referenced in some VB code.
    Debraj, other than it including the title cell (which should be excluded) it seemed to work at first as all the rows got included into the range, however when I added a new row of data it did not dynamically change to include it. How can I fix this?

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Dynamic name range not working correctly

    Try to add last data as TEXT.. will work.. if your data is mix of NUMBER & TEXT..
    give me some time..

  6. #6
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Dynamic name range not working correctly

    I just typed the data directly into the sheet manually.

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Dynamic name range not working correctly

    Table references works in VBA but it would require some work to convert I guess.
    A lazy solution, which I don't really like, is to use Tables and then assign your current names to Table references. My experience is that Tables are more reliable than named ranges. I hardly ever use dynamic named ranges anymore.

  8. #8
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Dynamic name range not working correctly

    try this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Dynamic name range not working correctly

    That fixed it thank you, although how can i tell it to remove the column title in row 1?

  10. #10
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Dynamic name range not working correctly

    =EquipmentData!$B$2:INDEX(B:B,LOOKUP(2,1/(Equipmen..
    Try this...

  11. #11
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Dynamic name range not working correctly

    Great, thank you.

+ 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] Sort dynamic range alphabetically - cant get it working
    By Taktiker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2013, 11:35 AM
  2. [SOLVED] Can't get nested Loop to copy range values from 2 tables working correctly
    By gtol in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2013, 12:01 PM
  3. Range. Find Not working correctly?
    By jvbeats in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2013, 12:21 PM
  4. How do I know if Dynamic Range is working?
    By livemusic in forum Excel General
    Replies: 9
    Last Post: 07-02-2011, 11:16 PM
  5. Subtotal method of Range not working correctly
    By NewToExcelVBA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2008, 03:08 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