Back to Excel Newsletter Archives SPECIAL! Back to Excel Newsletter Archives SPECIAL!
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
A great way to put documentation into the formula itself is to use the little known N Function. By using the N Function you can add to the end of your formula some text that will not interfere with the result, but will give you the information you require at a later stage.
Say you have the following formula in cell A11
=SUM($A$1:$A$10)*$B$1
Assume $A$1:$A$10 houses various numeric results that represent totals for a particular month, and $B$1 contains a percentage value that represents a tax rate. You could add some descriptive text to the formula using Excel’s N function:
=SUM($A$1:$A$10,N("Values for April"))*$B$1+N("Tax Rate for April")
Now you can determine what the formula is being used for simply by selecting this cell and looking in the Formula bar. TheN function always will return a value of0 for any text, and so does not interfere with the formula’s result in any way.
As most Excel users will know, the fill handle has a few built in lists already such as Days of the Week and Months of the Year. You can easily add your own custom list to Excel, so you can type the first item in the list, drag it down using the fill handle, and watch the list fill automatically.
The most flexible way to create a custom list is to enter the list contents into a range of cells. For example, say you have a list of 10 employee names. Enter each name, starting with cell A1 and ending with cell A10, and sort the list, if needed. Now go to Tools>Options>Custom Lists and click the collapse tool to the left of the Import button. Using the mouse pointer, left-click in cell A1 and drag all the way down to A100. Click the Collapse tool again, then click the Import button, then OK. From this point on, the custom list will be available to all workbooks on the same computer.
Once you create a custom list, you can turn the list upside down. To do this, return to the column next to the custom list and place the last entry from the list in the top cell. In the cell beneath it, place the second-to-last entry. Select both cells and double-click the fill handle. The list you produced should be reversed.
Quick Access to Excel Custom Lists. See Also: Adding a Command Button to the Excel Right Click Menu
Excel Custom Lists are a great way to quickly get a list of numbers or text onto a Worksheet. These are done via theExcel Fill Handle . Excel has built in Lists for Weekdays (Mon-Fri), Months (Jan-Dec) and numeric sequences. We are also able to add our own Custom Lists via Tools>Options - Custom Lists. However, once you have added a few of your own Custom Lists, it can be hard to remember the first item in the list that must be entered in a cell. This is necessary and then you can drag down via the Excel Fill Handle .
Code For Quick Access to Excel Custom Lists
The code below must be added to the Private Module of the Workbook Object (ThisWorkbook). To get there quickly, while in Excel proper right click on the Excel icon, top left next to File and choose View Code. It is in here the code immediately below must be placed.
Finance Templates 80% Off!Private Sub Workbook_SheetBeforeRightClick _(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Dim cBut As CommandBarButtonDim cControl As CommandBarControlDim lListCount As LongDim lCount As LongDim MyListDim strName As String On Error Resume Next With Application '' Uncomment below to reset back to default. ' .CommandBars("Cell").Reset For Each cControl In .CommandBars("Cell").Controls If cControl.Caption Like "*....*" Then cControl.Delete Next cControl lListCount = .CustomListCount For lCount = 1 To lListCount MyList = .GetCustomListContents(lCount) Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True) With cBut If UBound(MyList) = lListCount Then .Caption = MyList(1) & "...." & MyList(UBound(MyList) - 1) Else .Caption = MyList(1) & "...." & MyList(UBound(MyList)) End If .Style = msoButtonCaption .OnAction = "AddFirstList" End With Next lCount End With On Error GoTo 0End SubThen, add the code below to any standard public Module (Insert>Module)
Sub AddFirstList()Dim strList As String strList = Application.CommandBars.ActionControl.Caption If Not strList Like "*...*" Then Exit Sub ActiveCell = Left(strList, InStr(1, strList, ".", vbTextCompare) - 1)End Sub
Now, each time you right click on a cell you will see the first...last items in each Custom List. Then, when you click it, it places the first Custom List item into the active cell. Then you simply drag down via the Excel Fill Handleto get the rest of the list.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help