Hi,
i am wondering which formula will be faster to get dynamic range from list:
something like:
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)Please Login or Register to view this content.
Second approach working on whole column but offset is volatile function...
Or maybe there is another approach, the best of?
Paul
Last edited by Paulant; 02-12-2019 at 07:49 AM.
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
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
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 ?
This is for DV list. To have uniques there.
Paul
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:
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: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.
Maybe this will help:
It's more for examples of what you can do. Research the link to see lots more examples.Please Login or Register to view this content.
http://www.globaliconnect.com/excel/...=79&Itemid=475
Trevor Shuttleworth - Retired Excel/VBA Consultant
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
Thank you
are you sure? So putting this code:the above will only evaluate when the DV list is itself activated.
into named range also will be not generally volatile function?Please Login or Register to view this content.
Paul
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.
Correct; if you reference the Named Range in an in-cell calculation e.g. =ROWS("namedrange") then that calculation will be Volatile.Originally Posted by paulant
edit: added quote to keep logical chain of response.
Last edited by XLent; 02-12-2019 at 12:33 PM.
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.
You're welcome. Thanks for the rep.
O nice, i didnt realize how can i test it. Thank you!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.
Have a nice day
Paul
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks