Excel 2007 Hyperlink Limitation

For a couple of years now I’ve been pulling IT service desk statistics into a spreadsheet and setting up worksheets to break down and analyse the data in various ways.  The number of worksheets has subsequently grown a significant amount and I wanted to create a front worksheet that contained hyperlinks to and brief descriptions for each of the subsequent worksheets.

This is straight forward to do in Excel.  Press CTRL+K to bring up the hyperlink dialog and select the “Place in this document” set and then just select the worksheet you want the hyperlink to link to.  The issue I discovered was that this really does only apply to worksheets.  I had several chart sheets inserted into the workbook and these were not being listed in the hyperlink options.

As a result I’ve copied the charts from each of the chart sheets and pasted them as items on new worksheets.  An inelegant solution, but a practical one that doesn’t involve adding a macro to do the job.

Finding the size of a Microsoft Office 2007 file

Prior to Microsoft Office 2007 (MSO2007), office provided a quick and simple way to find out just how big your file was.  This was done by selecting the properties option from the file menu which then displayed the same file properties dialog box that you could select from the file in Windows Explorer.

In MSO2007 the information is still available, but has been annoyingly ‘hidden’ somewhat deeper in the application.  In fact it is so awkwardly hidden that I know many people have not come across how to access it … so I thought I’d share.

Along with the menu item click options I’ve included the accelerator / shortcut keys to help make this as quick as possible.

1. Select the ‘Office button’ [ALT+F].
2. Select the ‘Prepare’ menu item [Press E].
3. Select the ‘Properties’ menu item [Press P].
4. In the ‘Document Properties’ section that appears, click the title (“Document Properties”) of the section to reveal a drop down list containing just one item – ‘Advanced Properties’.  Select this item to display the file properties.

NB: Please keep in mind that until you save a file, the properties window will not provide a file size though other file properties may be populated.

Get Value from the Text Function in Excel

I’ve been doing a little bit more work with some spreadsheets recently and I came across a situation where I needed to work with a date and carry out a comparison to a string of text.  My first attempt fell short as it wouldn’t get the value I wanted, but Excel’s TEXT() function came to my rescue.

The issue came about like this.  I wanted to take a date in the format “dd/mm/yyyy” in one cell and display it in a short date format of “mmm-yy” in the cell next to it.

e.g.  “07/01/2010” in cell A1 and “Jan-10” in the neighbouring cell A2.

My original approach was to set the second cell to pick up the value of the first.  So taking the example above A1 would contain “07/01/2010” and cell A2 would contain a formula “=A1”.  I then set the formatting on cell A2 to a custom format “mmm-yy”.

I next wanted to check if cell A2 was equal to “Jan-10”.  This was where the issue arose as although cell A2 displays “Jan-10” its value is actually “07/01/2010″ … the value in cell A1.

The solution was to set the formatting of the A2 cell back to ‘general’ and then change its formula to… =TEXT(A1,”mmm-yy”)

The value of cell A2 is then “Jan-10”.

For me this highlights the difference between the content (what is typed into the cell, i.e. the formula), the value (what the formula evaluates to), and what is displayed (the value can be formatted to change how the user sees it on screen/printed).

Multiple Monitors

Several months ago I got a Dell Latitude E6400 laptop to replace my overworked Dell Latitude D410.  Along with the laptop I got a new advanced E-dock which included two monitor outputs, so I recycled an old smaller monitor that had been relegated to the stores to have a dual monitor set-up.

I find the two monitor set-up really useful and I frequently have different applications in different windows as you might expect.  However I struggled with Excel as I would frequently want to copy data between large format spreadsheets and I would have loved to have one on each screen … but they simply opened as child windows and I Alt-Tab’d between them.

I guess I never really stopped to think about it, but one day my subconscious kicked in and I realised that if I chose not to open the files directly I could open multiple instances and then open a file in each.  Then each instance of the application can be dragged to a different monitor.  Hey presto a spreadsheet in each window.

Excel: Last Entry

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

Example

So to start with I needed to know how many entries were in the list.  The COUNTA function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The OFFSET function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1.  The function we would use is simply =OFFSET(A1,COUNTA(A:A)-1,0,1,1)

However this does give us a #REF! if the list has no entries – which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an IF.

=IF(COUNT(A:A)=0,””,OFFSET(A1,COUNTA(A:A)-1,0,1,1))

Excel – Dynamic Named Range

I’ve recently been helping some colleagues develop some reporting templates and they had a very particular requirement around drop down lists (which I’ll probably post about at some point in the future).  One issue that did occur however was that the dynamic lists that were incorporated into the workbook were added to and even though I had mentioned to the users about using Excel’s name manager to redefine the range of cells with that name, they forgot and inevitably the workbook would end up back with me to rectify.

Use name manager to define the dynamic range

I decided that I’d try and find a way of defining a range so that it would automatically extend itself when another cell entry was added to the bottom of the list.  It turned out to be more straight forward than I’d initially thought it might be.  The statement below sets a range that will begin at cell “A2” on worksheet “Sheet1” and extend down for the number of cells that contain a text entry.  It assumes that the list is unbroken and that the top cell is the name of the list (hence the -1after the “COUNTA”). Whilst the name is not essential I include it as having the name of the list in the top cell allows me to prgramatically carry out list operations by referencing the top cell in a column.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Example list

The screen shot on the right shows a sample list of colours that has a range defined by using the function above.  A drop down list is automatically populated from this list and is shown in cell B2.

Excel – Dynamic Drop Down Lists with Full Validation

This post has now been migrated to ThoughtAsylum.com.

Follow this link to go directly to the article.

Excel – Multiple Worksheet Macros

A little while ago I was asked by one of our finance team to see if I could help reduce the workload required in maintaining a reporting Excel spreadsheet. Along with some very specific automation amendments to the spreadsheet I also created something quite generic to allow a macro to be run against multiple worksheets in an Excel workbook.

Originally finance were spending hours making amendments to 96 worksheets! This little helper allowed them to run the other amendments I made and process the whole workbook over their lunch hour (with time to spare).

Behind the scenes there’s a dialog box with a bit of VBA code that you call with the name of the macro you wish to run.   The spreadsheet includes a test function to show how to call the dialog box:

Select the macro to run

Public Sub MyTestMacro()
    LoopThroughWorkbooks ("MyMacro")
End Sub

Private Sub MyMacro()
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "xx"
End Sub

When the macro is called it will then display a dialog box with a list of the worksheets in the active workbook. Use CTRL and SHIFT to select the worksheets you want to run a macro against and then let it go.

Select worksheets

Once you’ve selected the worksheets, just click the Run button and sit back.  Excel will then simply activate each of the selected worksheets in turn and run the macro you’ve specified.

The whole thing is available in a form (frmRunMacroOnManySheets) and a code module (modLoop) for you to download in the spreadsheet below.  Simply copy these into a common workbook or the one in which you specifically need to run a macro against several worksheets.

• Download Spreadsheet (33.5 Kb)

If you do find this useful please include a link back to this blog.

Excel – Get Worksheet Name

I was working on an Excel spreadsheet a couple of days ago where I wanted to automatically pick up the name of the current worksheet to use in a VLOOKUP function.  I came up with the following combination of functions to get it for me…

RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))