Let me know what you think about the Video. How are you going to use Dynamic ranges in your Excel workbooks? Let me know what you are going to do with that skill below in the comments! If there's a certain topic you need me to cover, let me know on the comments below as well!
Where is the link to the Excel workbook? The link provided goes to your subscription page, not the workbook download. Do you mention in the video that the Offset is a volatile function and should be avoided with larger data sets because it can slow down calculation time?
Hi Michael, Thanks for the heads up. I have fixed the link. I have also mentioned at the end of the video that the OFFSET function is a volatile function. However, in my practical experience, I haven't encountered any slowdowns when using the OFFSET function. I have created dashboards with 10 and even 20 or more dynamic ranges using OFFSET and haven't experienced any slowdowns. As an Alternative, you could use INDEX to build dynamic ranges. Let me know if you need me to make a video about that.
I have experienced slow downs with performance but his might be due to a large number of array formulas I was using. Plus, not limiting the used ranges (i.e. referencing all the rows) makes a difference if you have a large file. I think the slowdown might occurr when you start dealing with lots of formulas and thousands of rows of data. I had one job where they had a P&L statement for each division (each sheet contained over 500 rows of data and 30 or more columns of vlookup formulas). It would take 5 minutes to save the file or 2 or 3 minutes to copy and paste data. I should have taken off the automatically calculation and set it to manual. It was a nightmare!
@@michaeldiamond2726 interesting. But it shouldn't necessarily be because of the offset function. Could be because of having lots of rows and array formulas. Anyways, thanks for passing by and let me know your feedback about my videos :)
Most dont know how to use this - good for you - good video. I have used this for years in combination with MATCH and can lookup data on a web page or in a document based on a single set of TEXT, which is so much easier than INDEX. We even use SEARCH at times which gets complicated but has many benefits. We pick out any text from any paragraph, or values from any table in an HTML or PDF etc. A great scraping tool.
Thanks, Marc! I'm glad that you found it helpful! It's the first time I hear that it can be used for web scraping as well! That's awesome! Thanks for commenting. Much Appreciated!
Very Well explained. I was struggling with the concept of dynamic named ranges using offset function. This video from the ground up helped clear my doubts, Thank You
Hi. I’ve gt a problem with the offset’s value error. I try to use 3 index matches in offset function for: reference, rows, cols. Separately all work correctly just when combined give an error. Also tried used Sum at the beginning of the formula syntax -no joy Any ideas to fix it or substitute? Many thanks
@@ExcelBonanza Please find the syntax =OFFSET(INDEX(50:50,MATCH($H$45,50:50,0)),INDEX(A:A,MATCH($I$45,A:A,0)),INDEX(52:52,MATCH($J$45,52:52,0)),1,4) - in the formula's window all matches give correct numbers of rows and columns but I've ended up with the value error
Let me know what you think about the Video.
How are you going to use Dynamic ranges in your Excel workbooks? Let me know what you are going to do with that skill below in the comments!
If there's a certain topic you need me to cover, let me know on the comments below as well!
Where is the link to the Excel workbook? The link provided goes to your subscription page, not the workbook download. Do you mention in the video that the Offset is a volatile function and should be avoided with larger data sets because it can slow down calculation time?
Hi Michael,
Thanks for the heads up. I have fixed the link.
I have also mentioned at the end of the video that the OFFSET function is a volatile function.
However, in my practical experience, I haven't encountered any slowdowns when using the OFFSET function. I have created dashboards with 10 and even 20 or more dynamic ranges using OFFSET and haven't experienced any slowdowns.
As an Alternative, you could use INDEX to build dynamic ranges. Let me know if you need me to make a video about that.
I have experienced slow downs with performance but his might be due to a large number of array formulas I was using. Plus, not limiting the used ranges (i.e. referencing all the rows) makes a difference if you have a large file. I think the slowdown might occurr when you start dealing with lots of formulas and thousands of rows of data. I had one job where they had a P&L statement for each division (each sheet contained over 500 rows of data and 30 or more columns of vlookup formulas). It would take 5 minutes to save the file or 2 or 3 minutes to copy and paste data. I should have taken off the automatically calculation and set it to manual. It was a nightmare!
@@michaeldiamond2726 interesting. But it shouldn't necessarily be because of the offset function. Could be because of having lots of rows and array formulas. Anyways, thanks for passing by and let me know your feedback about my videos :)
I clicked on the new link and it sent me to the IndexMatch video, not the Excel workbook. Might want to check on that ...
Most dont know how to use this - good for you - good video. I have used this for years in combination with MATCH and can lookup data on a web page or in a document based on a single set of TEXT, which is so much easier than INDEX. We even use SEARCH at times which gets complicated but has many benefits. We pick out any text from any paragraph, or values from any table in an HTML or PDF etc.
A great scraping tool.
Thanks, Marc! I'm glad that you found it helpful! It's the first time I hear that it can be used for web scraping as well! That's awesome! Thanks for commenting. Much Appreciated!
In my opinion the best explanation of the Offset function on RUclips.
Thanks, David! I'm glad you liked it!
Very Well explained.
I was struggling with the concept of dynamic named ranges using offset function.
This video from the ground up helped clear my doubts,
Thank You
Thank you! I'm glad that you found the video helpful. Please let me know if there's a certain topic that you would like me to create a video about.
Very nicely explained! Great Job!!
Really good explained....would love to look forward for Index match and array functions as well
I do have an INDEX Match video on the channel. Check it out!
Hi. I’ve gt a problem with the offset’s value error. I try to use 3 index matches in offset function for: reference, rows, cols. Separately all work correctly just when combined give an error. Also tried used Sum at the beginning of the formula syntax -no joy
Any ideas to fix it or substitute? Many thanks
What version of office are you using?
@@ExcelBonanza Hi 365
The inputs for the Offset function should produce integers. Is this the case on your INDEX - Match formulas?
@@ExcelBonanza Please find the syntax =OFFSET(INDEX(50:50,MATCH($H$45,50:50,0)),INDEX(A:A,MATCH($I$45,A:A,0)),INDEX(52:52,MATCH($J$45,52:52,0)),1,4) - in the formula's window all matches give correct numbers of rows and columns but I've ended up with the value error
جزاك الله خيرا
Thank you sir
+M. Alomery جزانا وإياكم
My pleasure :)
In fact we even use INDIRECT to find the best starting point that we know has our data.
Thanks, Marc!
tnx
My pleasure!
in this example how would you treat with changing criteria in the rows as you move across columns eg>= 15 but =20 but =25 but =15 =20 =25
Sorry. Don't understand the question