ALL YOUR EXCEL NEEDS
|FREE Excel STUFF|
NEW EXCEL VIDEO TRAINING-HALF PRICE FOR SHORT TIME!
Rather than typing those mega long formulas in the result file,
that reference outside Workbooks, type it in the "outside Workbook" then Cut and
paste to the "result file"
Copy formulas from one Workbook to another. Open both Workbooks and select the formula range. Now, CUT, not copy, the formulas and paste into the other. IMPORTANT: close the formula Workbook you CUT from WITHOUT SAVING.
Toggle through Relative and Absolute Range reference. Select the cell and then, in the formula bar, click the range reference and push F4 until the reference type you need is seen.
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 (*Excel 2007) 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 A10. 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.
*Click the Microsoft Office Button, and then click Excel Options. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
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
The list you produced should be reversed
Create a custom list of the Alphabet. Enter =CHAR(98-ROW()) in A1 and Fill Down. Copy the Alphabet list and Paste Special - Values. For upper case letters, use =CHAR(ROW()+64)
Very similar to the above with the use of the CHAR Function, but you use =CHAR(ROW()) in A1 and then Fill Down until you get the #VALUE! Error. Now copy the list of special characters and Paste Special - Values.
Often, when you copy or import data into Excel, you end up with some special characters that are not wanted. Sometimes, you cannot even see them, but they cause grief when they are referenced by formulae. For the ones you can see, select the cell and then from the Formula Bar, highlighted the special character and copy (Ctrl+C). Now show Find & Replace (Ctrl+H) and in the Find What box paste the special character (Ctrl+V), and leave the Replace With box blank. Now click Replace All. The same applies to the special characters you cannot see, but as you cannot see them you highlight, what appears to be a space, from the Formula Bar.
Select the formula range and show Find & Replace (Ctrl+H). In the Find What box type = and in the Replace With box type # or any non common character that does NOT appear in any of your formulas. Now copy, what were formulas, and Paste Special - Transpose. Now simply select the transposed range and reverse the Find & Replace you used above.
Excel Custom Lists are a great way to quickly get a list of numbers or text onto a Worksheet. These are done via the Excel 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.
Private Sub Workbook_SheetBeforeRightClick _ (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim cBut As CommandBarButton Dim cControl As CommandBarControl Dim lListCount As Long Dim lCount As Long Dim MyList Dim 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 0 End Sub
Then, 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 Handle to get the rest of the list.
See Also: Adding a Command Button to the Excel Right Click Menu
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft