MS Excel Split Postcode

If anyone is stuck with anything Excel or Access related then send me a note and I will try and help, I love Excel and am always happy to try and discover new skills.

On this note I was asked a little while ago how to split a postcode in Excel, as it was put to me:

"How do you split the first part of the postcode out when it can sometimes be 4 digits and sometimes 3 digits."

Now there is a way to do this using isnumeric - but I found a much simpler way is to look at the part of the postcode you know doesn't change which is the last three digits, the last three digits are always three so therefore you know that the space will always come from the fourth space from the right.

=LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3) 

Here what I am doing is trimming off all but three digits from the left and then concatenating the three right most after adding the space so 

RH11RH, RH111RH and R111RH will become RH1 1RH, RH11 1RH and R11 1RH respectively.

If you want to do the opposite then you can use this formula

=REPLACE(A1,FIND(" ",A1),1,"")  to take the space out of a postcode.

Other useful Postcode formula include

=LEFT(G2,FIND(" ",G2)-1)  to find the outcode of a postcode that contains a Space or simply

=LEFT(A1,LEN(A1)-3) to find the out code of a Postcode without a space.

  

Automatically Sort Excel Range

Had another Excel request through – You wait a month for one then two come through at once.

This time the request is how to make a sheet update automatically when a certain cell is updated, This is how I did it (Although in all honesty there are hundreds of snippets on the net if you look for them.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets(8).Range("N:N"), Target) Is Nothing) Then ‘Change this Range to contain the cells you want it to trigger for
AutoSort ‘This calls the sub procedure below which does the actual work
End If
End Sub

Private Sub DoSort()
Worksheets(8).Range("L2:O49").Sort Key1:=Worksheets(8).Range("L2"), Order1:=xlAscending, Header:=xlNo 'This line does the sort as requested in this case sorting the table L2:O49 by the field in column L2 – All of these calculations take affect on Worksheet 8.
End Sub

Handy Sub to Unlock Protected Excel Worksheet

Had a request a few weeks ago from someone who needed to unprotect an Excel sheet which had been protected by someone who was no longer with the company so they could not get the Password.

here is the procedure I developed,

Sub PasswordReset()

Dim ia As Integer, ib As Integer, ic As Integer
Dim id As Integer, ie As Integer, ig As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For ia = 65 To 66: For ib = 65 To 66: For ic = 65 To 66
For id = 65 To 66: For ie = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For ig = 32 To 126
ActiveSheet.Unprotect Chr(ia) & Chr(ib) & Chr(ic) & _
Chr(id) & Chr(ie) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(ig)
If ActiveSheet.ProtectContents = False Then
MsgBox "Your Sheet has Been Unlocked Using " & Chr(ia) & Chr(ib) & _
Chr(ic) & Chr(id) & Chr(ie) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(ig)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

So next time you forget your password and need to unprotect a sheet just add this to the VBA window and run the Module. Hey Presto.

Top 10 "Secret" Office features

I came across a website with the Top 10 "Secret" features of Microsoft Office for me the biggest shock is that these things were considered secrets, I have to admit I only found 1 thing of interest - That when you drag a cell around in excel #6, you can hold down control to copy - the rest however are mostly elements such as Format Painter, Search, Presentation View which are all basic toolbar options.

I may have to start work on my own list of top 10, I think my list would contain stuff as for example in Excel to copy the details of the Cell above by pressing Ctrl + D, or that you can switch between pages by pressing Ctrl and PgDn

Anyway, Post your suggestions and I will start compiling a Top Ten - That does not include menu items.

Glookup Function - Combining Vlookup and Hlookup

I have been working on a new Excel function of late as I have always liked Vlookup and Hlookup but as discussed in an earlier post the more you try and do excel the less use they become, the issues I was finding more and more is that I used Match and Offset far more often. The main problem I had is that Vlookup will find a value in the left hand column and return a value, and Hlookup finds a value at the top but what if you have a grid and need to select a value based on a V and an H lookup?

In this case you use Match to find the first value and then use Count and Offset to create the dynamic range for the second, this was fine for one calculation but after writing 100 formulas on each sheet it becomes a pain. Therefore I worked on my new function.

Glookup (Grid Lookup) it works on the same lines as V and H lookup only the two lookup values are also the column return counts.

Here is the Formula broken down =Glookup([Vlookup],[Hlookup],[GArray])

Where Vlookup is the value in the Left Hand Column

Hlookup is Value in Top Row

GArray is the Grid Array (Where your data is – Must include your two header rows)

In the above example therefore =glookup("Feb",2007,A2:K14) would have the result of 23

So how does it work? Firstly I have to say in the interest of fairness and because it would simply be wrong not to, I have to say a big vote of thanks to RomperStomper over at Excelhelp.net who helped me with tidying up the code into a very nice succinct module.

Anyway to use Glookup simply paste the following code into the VBA window of your spreadsheet (Pressing Alt + F11 or going to Tools-Macro-Visual Basic Editor)

Function GLookup(VLookup, HLookup, GArray As Range)

Dim VRef

Dim HRef

VRef = WorksheetFunction.Match(VLookup, GArray.Columns(1), 0)

If IsError(VRef) Then

GLookup = CVErr(xlErrValue)

Else

HRef = WorksheetFunction.Match(HLookup, GArray.Rows(1), 0)

If IsError(HRef) Then

GLookup = CVErr(xlErrValue)

Else

GLookup = WorksheetFunction.Index(GArray, VRef, HRef)

End If

End If

End Function

It should now look like this

Now you can call the function from any worksheet in this book, don’t forget though you will unfortunately have to copy the function into each sheet you want to use it on.

Returning Quaterly Values In Excel

I was speaking to a friend earlier who showed me a new way to automatically display quarterly totals using Sum Product.

This Table is set up in A2:D14

In the Quarters Summery sheet enter this formula

=SUMPRODUCT((ROUNDUP(MONTH(A3:A14)/3,0)=2)*(C3:C14))

Now this function has the same effect as simply inputting =SUM(C3:C5) but has the advantage that you can have the same formula in each cell, you select which Quarter you want to return by adjusting the =2 to show which quarter you require (i.e Jan - March is 1, Apr - Jun is 2 etc...)

I think perhaps that the best use for this however is to discourage the occasional excel user from trying to "Improve" your worksheets.

Nerd Humour and Cross Tab Issue

First a little Nerd joke.

"There are only 10 types of people in the world, those who understand binary, and those who don't."

Anyway,

Have had a slight issue this morning trying to load a crosstab query into a recordset to append to an excel sheet - When does this not bother me at 6am on a Sunday!!

Anyway the issue is in trying to transfer the information to a spreadsheet with the column heads, obviously it is quite easy to transfer the data but the column heads are trickier as with a cross tab query they could be different each time. anyway I managed to sort out a code snip which will populate the column headings.

Private Sub TransferCrossTab()

Dim myQdef As QueryDef
Dim rsReport As Recordset
Dim appXl As Excel.Application
Dim appxlWB As Excel.Workbook
Dim appXlWS As Excel.Worksheet
Dim lngColNbr As Long
Dim xlc As Object

Set myQdef = CurrentDb.QueryDefs("qry_monthlyquery_agb") 'open crosstab query
Set appXl = CreateObject("Excel.Application")
Set appxlWB = appXl.Workbooks.Open(strfilepath, , False)
'open spreadsheet "Here Stored as global variable"
Set appXlWS = appxlWB.Sheets("Source_Data")
Set xlStart = appXlWS.Range("B1")

appXlWS.Range("B1:AY453").ClearContents 'Clear Contents from Spreadsheet
For lngcolNbr = 0 To rsReport.Fields.Count - 1
xlStart.Offset(0, lngcolnbr).Value = rsReport.Fields(lngcolnbr).Name 'For Each Field in Recordset provide column heading
Next lngcolnbr
Set xlc = xlc.Offset(1, 0)
appXlWS.Range("B2").CopyFromRecordset rsReport 'Add Recordset Data

End Sub

Hope this makes sense, Haven't really got time to go over it at present but if this sounds like it may help you feel free to get in touch.

Access Date Format Issues

I have been asked by a few people how to solve the issue that when you are sending data from Excel to Access (and vice versa) in VBA the dates get flipped into US format :- MM/DD/YYYY instead of DD/MM/YYYY now this will only happen when you the day of the date is less than 12, the issue comes from the way the Excel or Access interperate the date it recieves. annoyingly it does not give any regard to the regional settings.

Anyway the way I found to get around it is using a function to convert the date into a format that can not confuse the system.

Public Function Fixdate(ByVal str)

Dim dteDay
Dim dteMonth
Dim dteYear

If isnull(str) Then
msgbox "Error"
Exit Function
End If

dteDay = Day(str)
dteMonth = MonthName(Month(str), True)
dteYear = Year(str)

FixDate = dteDay & "-" & dteMonth & "-" & dteYear

End Function

This is then used as follows

myDate = fixdate(date)

This will return a value to mydate as 25-May-2010 which Excel or Access will be able to interperate as the correct date.

World Cup Tracker and Income Tax Calculator

I have just uploaded two files to my Business website http://www.adamgbrown.com/ I have a world cup tracker file and an Income Tax Calculator. Please check them out and let me know what you think.


Here are some Screen Shots

World Cup Tracker, Just Enter the Scores and everything else will update

Work Out How Much Income Tax you should be paying

Vlookup is Dead, Long Live Flexible Lookup

When I first found Vlookup I was over joyed with the simplicity and beauty of a string of IFs and Vlookups. I even claimed grandly that there was nothing that could not be done with these two friends of Excel users. However there is a very good chance that I have used my last Vlookup!

The reason I could turn away from my most loyal of Excel functions, the reasons are three fold:

Match

Offset

Indirect

I will demonstrate how I use them by using examples from a spreadsheet I have to calculate your BMI range from your weight (Weights have been changed to protect the Cake Loving.)

Match will return the position of a value in a list e.g. =MATCH(J30,A1:A24) will locate the position of J30 in this case the height in the range A1:A24 the answer is in this case 17 (Which is also the Row number)

Indirect lets you use the result of a Formula as a Cell Reference e.g. INDIRECT("A"&Match(J30,A1:A24)&":R23") As you can see from the attached function arguments this presents itself as a range from A17:R23 now if you change the value in J30 say to 70 you will now get the Dynamic Range A16:R23 and so on.

Offset will return a value a specified number of rows from a given point. e.g. =OFFSET(A1,3,2) will give you the value found in Cell C4. obviously this is not as easy as writing =C4 but if we combine all three functions

=OFFSET(INDIRECT("A"&MATCH(J30,A1:A24)),24-MATCH(J30,A1:A24),5)

Then we have an alternative to Hlookup (In this example I have just entered a 5 in the Rows criteria however for a truly dynamic range you would use another Match() )

So This Function is basically looking up the row the data in J30 is in and then returning the value which is at the bottom of the grid in the specified column. In this case "OK Weight"

Another good use for the Offset function is to create a dynamic named range to automatically update graphs =OFFSET(Daily!$AQ$2,1,1,COUNT(Daily!$K$2:$K$191),1) This function creates a range that starts in AQ2 and is 1 column wide by however many rows deep have data (In Column K) But I may write more about that at a later date.

The Joys of INDIRECT Excel Function

I have just been working on a spreadsheet and have finally solved an issue that has bugged me for a while. I know this has been quite obvious but I have been very slow where this is concerned.

If you want to select a total from each sheet in a workbook you can easily select it using the INDIRECT function.

If you have a spreadsheet containing data on each page, called January, February etc... and you want to make a summery sheet you can use this formula.

=SUM(INDIRECT(L4&"!$H$3:$J$47")) where column L contains your month name and each sheet contains the data you want summed in the grid H3:J47 then when you copy down the month names it will automatically address the range to the correct sheet.

I can't believe I have only just realised this would work.