+ Reply to Thread
Results 1 to 14 of 14

Formula to get dynamic named range comparison

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Formula to get dynamic named range comparison

    Hi,

    i am wondering which formula will be faster to get dynamic range from list:

    something like:

    Please Login or Register  to view this content.
    or something like:

    Please Login or Register  to view this content.
    In first function i am providing row ($U$150) which has to be much higher than the end of list (because in the list i can add new data and rows)
    Second approach working on whole column but offset is volatile function...

    Or maybe there is another approach, the best of?

    Paul
    Attached Files Attached Files
    Last edited by Paulant; 02-12-2019 at 07:49 AM.

  2. #2
    Forum Moderator
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    6,379

    Re: Formula to get dynamic named range comparison

    Your example file does not really help
    OFFSET is volatile perhaps best to be avoided
    Why not define your range as a Table? Normally the formula in which you want to use the dynamic range should adapt automatically

    If you can post a more representative example of what you are trying to do, it would be easier to help

  3. #3
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Re: Formula to get dynamic named range comparison

    Hi,

    example attached.
    Because the list is created as pivot table (unique and sort data) and it is not possible to refer to pivot table.

    Paul
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    6,379

    Re: Formula to get dynamic named range comparison

    So you want to use a Pivot Table as dynamic range? To what purpose?
    Could you add an example in your sheet of what it is to be used for ?

  5. #5
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Re: Formula to get dynamic named range comparison

    This is for DV list. To have uniques there.

    Paul

  6. #6
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    471

    Re: Formula to get dynamic named range comparison

    If you're always going to have a Grand Total, and you're not going to have other Pivots below that Pivot (i.e. Col A dedicated to your server Pivot) then create a Defined Name:

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


    then use the above as your DV source: =_Servers

    Alternatively you could just use the below as your DV source:

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


    Used in the above context OFFSET is, in fact, non-volatile -- i.e. the above will only evaluate when the DV list is itself activated.
    Last edited by XLent; 02-12-2019 at 09:29 AM.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,919

    Re: Formula to get dynamic named range comparison

    Maybe this will help:

    Please Login or Register  to view this content.
    It's more for examples of what you can do. Research the link to see lots more examples.
    http://www.globaliconnect.com/excel/...=79&Itemid=475
    Attached Files Attached Files
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Re: Formula to get dynamic named range comparison

    Thank you

    the above will only evaluate when the DV list is itself activated.
    are you sure? So putting this code:

    Please Login or Register  to view this content.
    into named range also will be not generally volatile function?

    Paul

  9. #9
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Re: Formula to get dynamic named range comparison

    I do not think so, named range will be volatile:

    A volatile function within a cell or named range is recalculated every time any cell in the worksheet recalculates (instead of just when one of the function's arguments changes). Also, any formula that depends on a cell containing a volatile function is also recalculated.

  10. #10
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    471

    Re: Formula to get dynamic named range comparison

    Quote Originally Posted by paulant
    "...are you sure?"...
    Correct; if you reference the Named Range in an in-cell calculation e.g. =ROWS("namedrange") then that calculation will be Volatile.

    edit: added quote to keep logical chain of response.
    Last edited by XLent; 02-12-2019 at 12:33 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    471

    Re: Formula to get dynamic named range comparison

    Paulant, a simple test for Volatility

    1. Save File
    2. Close File
    3. Open File
    4. Close File

    if at Step 4 you are prompted to Save you have Volatility.

    You will find that if you use the OFFSET construct to define a Named Range, and you use the Named Range to source a DV list (or not at all) you will not be prompted to Save.

    Only if you use the Named Range in a cell function will you get the Volatility.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,919

    Re: Formula to get dynamic named range comparison

    You're welcome. Thanks for the rep.

  13. #13
    Registered User
    Join Date
    02-05-2019
    Location
    Czech
    MS-Off Ver
    2016
    Posts
    9

    Re: Formula to get dynamic named range comparison

    Paulant, a simple test for Volatility

    1. Save File
    2. Close File
    3. Open File
    4. Close File

    if at Step 4 you are prompted to Save you have Volatility.

    You will find that if you use the OFFSET construct to define a Named Range, and you use the Named Range to source a DV list (or not at all) you will not be prompted to Save.

    Only if you use the Named Range in a cell function will you get the Volatility.
    O nice, i didnt realize how can i test it. Thank you!
    Have a nice day

    Paul

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,919

    Re: Formula to get dynamic named range comparison

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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