Dynamic list using Tables

Dynamic list

to create a list:: you can select data validation -> list -> select a range of cells as source(you can even you named range)

for dynamic list one way is to create a table.

How To create table
 In the  following example i created a table of players and Country.(i would use only player in list).
TO create a table once the data is entered in tubular format.
select the cells and press ctrl + t, select the option that says mytable has headers(top row (name,country).
by default excel will name the table automaticaly like table1,table2...so on.

You can rename the table by selecting the cell range and changing the name from table name box.

To create a list

simply press use DATA validation from menu - > select list - > use indirect function in source.(= indirect("name of table[name of column/field]").

as soon as you add new record to the table that is reflected in your list as well.

DYNAMIC LIST is READY

Make a habit of creating a table and naming them. This would help in data filters,validation , dynamic list,dynamic charts, pivot tables and in many complex query resolution using functions.

Windows tips : TasKKill / SHUTdown / keyBOARD shortcut

We all know that virtually all the windows OS freezes.

One quick solution to kill the frozen application to free the resources.

we would need to create a desktop shortcut for the same

TASKKILL


Place a mouse cursor on desktop empty- blank space.
Right click(mouse) on  desktop(empty space).
Select new shortcut from the list and type(in the box) : taskkill.exe /f /fi "status eq not responding"
CLICK NEXT

it will create a shortcut on your desktop(you can also assign keyboard shortcut to it)...Doulbe click on shortcut will kill the frozen or hanged program thats not responding




-----------------------------


SHUT down


create a shortcut to shutdown the computer.


Repeat the above steps  type the command in the name box : shutdown.exe -s -t 0


Tip :: you can mention full path as well if not working properly(based on OS)
C:\Windows\System32\shutdown.exe -s -t 0


--------------------------------------------


KEY BOARD Shortcuts

keyboard shortcuts for the above commands : right click on the icon(placed on desktop)  select properties and hold the combination of keys and press alphabet of your choise to create a short cut.

Windows tips

We all know that virtually all the windows OS freezes.

One quick solution to kill the frozen application to free the resources.

we would need to create a desktop shortcut for the same

Place a mouse cursor on desktop empty- blank space.
Right click(mouse) on  desktop(empty space).
Select new shortcut from the list and type(in the box) ;;;;;;;; taskkill.exe /f /fi "status eq not responding"
CLICK NEXT

it will create a shortcut on your desktop(you can also assign keyboard shortcut to it)...Doulbe click on shortcut will kill the frozen or hanged program thats not responding

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).....

Tips on windows,Excel, Access, POWERPOINT, VBA