DYNAMIC Name range

Named range are used to ease calculations and save time. Instead of selecting ranges for calculating or to retrieve a search value we can use name range. naming range is simple process. select the range of cells and just type the name of range in the name box(top left of the screen on formula bar...the box that displays cell address). or select range and press ctrl + f3 to name the range or use define name option from the ribbon(formula).

offset function::: Offset function ::: return a a reference to a range it takes 5 parameters  reference(first position of cells), no of rows to move, No of columns to move,height of range ,width of the range

PROBLEM :
in above example column a contains week and column b contains sales figures. we want to calculate the sales and put it  e4. we have 3,4 sales figure in col b(from b2). we want to create a named range that can be extended dynamically as soon as new sales no is added to the col.

SOLUTION :

Select define name (enter the name as sales or whatever you want ) in refer to box enter this function

= Offset(b2,0,0,count($b:$b))

in e2 type =sum(sales)

add few more sales no in b column and see.

tip : if you want to move row wise(ie 1 column) you would use height. if you know that you need to move 5 rows you would use 5 as a height else use a count function(inside offset) to count no of rows to move


>>>>> many more dynamic range,list,filters,tables,pivot tables,charts will come......thanks...will soon launch video tutorial for the same.
>>>>>  coming soon Advance excel(macro).....