To see 19 examples of all types on OFFSET Function, click here.<http://www.excelitems.com/2011/01/use-offset-function-dynamic-range.html>
The OFFSET function is a kind of lookup function in Excel that allows us to find a value in a table based on a particular reference point. This is different from Offset function used in VBA. In VBA, we can only refer a single cell from another cell but when used as Excel formula, it becomes one of the most important to learn. It is used in conjunction with named ranges, charts(to make them dynamic), Sum formula, SUMIF formula, Pivot Tables (to make source range dynamic), VLookup Formula, Array formulas etc. My next 4-5 posts will concentrate on full use of OFFSET function and I promise you to make expert in all tricks related to OFFSET Function. You tell the OFFSET function how far to move from the initial reference point (in terms of columns and rows) in order to find the required value. OFFSET function depending on the inputs may return a single cell or a bunch of cells (Range array). We talked a lot about OFFSET function, now let's have a look on it. i.e. How it works? What are its input and output options? etc etc. Syntax of the OFFSET Function: *OFFSET(initial reference, # of rows(to move), # of cols(to move), height, width)* ** initial reference *– The cell or group of cells that you would like to use as the initial reference for the OFFSET function. However, you can choose any reference cell you like but generally corner cells are taken in real world examples. ** # of rows* – This is a numeric value(strictly integer) that represents the number of rows you want to move to find your OFFSET function value. If you are moving down one or more rows, this value should be positive. If moving up, then the value will be negative. ** # of cols *– Exactly, like the rows argument, this is a numeric value that represents the number of columns you want to move to find the function result. If moving to the right, this value should be positive. Negative values represent moving to the left. * *height(optional) *– This value is optional and denotes the number of rows you want included in the OFFSET function result. 1 by default, if skipped. * *width(optional)* – This value is optional as well and denotes the number of columns you want to include in the OFFSET function result. 1 by default, if skipped. If any one of the height and width is more than 1, then Offset function must be used in conjunction with named range (to make source data of charts, pivots etc. dynamic) or array formulas because then it will not return a single cell but a bunch of cells. To see 19 examples of all types on OFFSET Function, click here.<http://www.excelitems.com/2011/01/use-offset-function-dynamic-range.html> ------------------------------ Excel VBA Interview Questions<http://www.excelitems.com/2010/12/excel-vba-interview-questions.html> Collection of Excel VBA Macros<http://www.excelitems.com/2008/09/vba-macro-codes-examples-collection.html> VLOOKUP <http://www.excelitems.com/2009/03/how-to-use-vlookup-function.html> OFFSET<http://www.excelitems.com/2011/01/use-offset-function-dynamic-range.html#more> ARRAY formulas<http://www.excelitems.com/2010/06/array-or-cse-formulas-and-functions-in.html> ------------------------------ Excel 2003 Style Menu in Excel 2007<http://www.excelitems.com/2010/12/excel-2003-style-menu-in-excel-20072010.html> Custom Number Format<http://www.excelitems.com/2011/01/custom-cell-formatting-number-date-text.html> SUMIFS<http://www.excelitems.com/2009/09/difference-between-sumif-and-sumifs.html> CONTACT a TRAINER<http://www.excelitems.com/2009/03/callcontact-trainer-for-excelvbavsto.html> ------------------------------ Thanks & Regards ------------------------------ Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com Facebook <http://www.facebook.com/eXceLiTems> Twitter<http://www.twitter.com/eXceLiTems> LinkedIn <http://in.linkedin.com/in/ashishjain08> LiveJournal<http://livejournal.excelitems.com> Digg <http://digg.com/excelitems> Delicious<http://www.delicious.com/save?new=1&url=http%3A%2F%2Fwww.excelitems.com%2F&title=MS%20Excel%20VBA%20%26%20VSTO%20Macros%2C%20Functions%2C%20Tutorials%20%26%20Downloads&type=BOOKMARK&jump=http://www.delicious.com%2Fmountint&key=Nyn3Hc9oMaEzmGO_Fg_jkAwe1hI-©user=mountint&via=mountint©tags=eXceLitems+Office+Microsoft+Functions+Formulas+VBA+VSTO+Excel2010+Excel2007+excel2003+Excel+Bookmarks_Menu+eXceLiTems.com> Subscribe to Email<http://feedburner.google.com/fb/a/mailverify?uri=excelitems> ------------------------------ -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
