Why Excel? Because so many companies in the UK rely on Excel to perform many of the financial tasks that should be done from ERP systems. This means that being a “spreadsheet jockey” is an essential skill in many companies.
These pages will provide tips for Excel users from me and other power users.
Excel Assist .co.uk
Excel hints, Excel tips and Excel assistance
An easier way to flip FirstName LastName to LastName, FirstName
Is there an easier way to flip FirstName LastName to LastName,First Name besides running a macro? There must be a simpler formula – any advice?
via Is there an easier way to flip FirstName LastName to LastName,First Name besides running a macro? There must be a simpler formula – any advice? | LinkedIn.
Try this
=MID(A1,SEARCH(” “, [...]
Compare two lists
I was asked the following question by a colleague today.
I have two spreadsheets and I have to check which part number is listed on both. What?s the quickest way apart from: copy party number go onto other spreadsheet do CTRL find and paste part number and do find all?
I recommended the Match Function.
MATCH(lookup_value,lookup_array,match_type)
Lookup_value [...]
Conditionally hide/unhide rows
I cobbled this code together for someone at work. They wanted to unhide rows if some cells contained Yes.
The control cells were D4, D5 & D6. You should be able to reuse this code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(“$A$29:$C$71″).EntireRow.Hidden = True ‘set the range to hide
[...]
Excel Function Dictionary 150 example functions and formula.
Peter Noneley Excel Function Dictionary Updated Oct 2009 – no macros or VBA code, smaller file size. The Excel Function Dictionary contains over 150 examples of functions.
via www.xlfdic.com Excel Function Dictionary 150 example functions and formula..
Click to Download
Function/Formula to Sum Excel Cells by Colour
Function SumColor(rColor As Range, rSumRange As Range)
”””””””””””””””””””
‘Written by Ozgrid Business Applications
‘www.ozgrid.com
‘Sums cells based on a specified fill color.
”””””””””””””””””””’
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult
End Function
via 2 Excel Functions/Formulas to Count/Sum Excel Cells by Color – Excel [...]
