In an Excel file with lots of worksheets, how do you help users navigate through the workbook?
Here are a few of the methods I’ve used. and I’d be interested in hearing about them. I’m sure you’ve found your own creative ways to deal with the Excel workbook navigation problem.
A couple of years ago, a client sent me an invitation to LinkedIn, a business connection website. To be polite, I joined, but never visited the site again, and didn’t make any further connections.
A few months ago, I looked at LinkedIn again. I can’t remember why — maybe someone else invited me to connect, or I saw it mentioned in a blog post somewhere. I searched a few names, found people who I know, and sent them invitations to connect.
I still didn’t see much value in the website, but visited about once a week, to see what people were up to, and to try to find a few more old contacts.
New Features Added
LinkedIn also has groups that you can join. Some are invitation only, and others are public. The groups were an interesting way to find other people with similar interests, such as Excel Users, but not much use beyond that.
Recently though, discussions were added to the groups, so people can post a comment or question, and other members of the group can respond. Finally, there’s some value in belonging to the groups, if members are active in the discussions.
I’m in an alumni group, and a couple of Excel groups, and the discussions are lively and interesting. I visit a couple of times a week, and have made a few new contacts through the group discussions.
What will I do with all these contacts? Who knows? Maybe they’ll lead to consulting opportunities, or resources when I’m stuck on a project.
Do you choose Save As, and save the file with a different name?
Something else?
Macro to SaveAsCopy
I like to have different versions of a file, so I can go back to a previous version if something goes horribly wrong.
So, I created a macro to save my files, and added a button to the Quick Access Toolbar (QAT).
What the Macro Does
The macro saves a copy of the active file in a specified folder, adding the year, month, day, hour and minute to the file name.
For example, if the file I’m working on is named Budget2009.xls, the backup file would be named Budget2009_20081215_1008.xls if I saved it at 10:08 AM today.
NOTE: This macro does not make any changes to the active workbook, so it does NOT wipe out the Undo stack in my version of Excel. Test this on your own computer though, to make sure it’s the same for you!
In the code, you can change the Save directory to one that you prefer on your computer or network.
NOTE: I use C:\Backups\, but you could change that to another directory that you use.
Sub SaveBUCopy()
Dim strFile As String
Dim strName As String
Dim lExt As Long
Dim strDir As String
Dim strExt As String
strName = ActiveWorkbook.Name
strDir = "C:\Backups\"
If UCase(Right(strName, 4)) = ".XLS" Then
lExt = 4
Else
lExt = 5
End If
strFile = Left(strName, Len(strName) - lExt)
strExt = Right(strName, lExt)
ActiveWorkbook.SaveCopyAs strDir & strFile _
& Format(Now, "_yyyymmdd_HhMm") & strExt
End Sub
Add Button to QAT
After you’ve added the SaveBUCopy macro to your workbook, and changed the directory name, make the macro easy to run.
To do that, follow the steps in this video, to add your macro to the Quick Access Toolbar.
No, you don’t necessarily need treatment just because you like using Excel. 😉 Today’s post shows an Excel workbook that you can download, to plan a sequence of treatments, prescribed by your doctor.
Excel Treatment Calendar Workbook
In response to a newsgroup posting, Roger Govier created a workbook to help patients plan a sequence of treatments.
For example, people taking Warfarin, might be prescribed to take doses of 2 mg, 2mg, 3mg, 2mg, 5mg and then go back to the start of the sequence.
Sequence Setup Sheet
In the sample workbook, they would enter that sequence on the Setup sheet.
Treatment Sites List
Muscular Dystrophy sufferers need to inject at different sites on the body each day.
They could list the sites on the setup sheet, in the Treatment_List column.
Then, after the sequence has been entered, click the “Fill Treatments Column” button.
That button runs an Excel macro, which has the sequence copied down, to fill the Treatments Column.
View Treatments on a Calendar
After the treatment list is created, switch to the Calendar sheet, to set up the current month.
First, from the drop down lists, select a year and month
Next, pick the first treatment for the month.
The calendar will fill in with dates and treatment sequence.
Download the Sample File
You can download Roger’s sample file from the Excel Sample Spreadsheets page on my Contextures site.
In the Functions section, look for FN0016 – Treatment Calendar
________________________________
In a pivot table, you might have a few row labels or column labels that contain the text “(blank)”. This happens if data is missing in the source data. For example, in the source data, there might be a few sales orders that don’t have a Store number entered.
The UK Excel User Group is holding a free conference at Microsoft London in April 2009. It’s a bit too far for me, but if you can make it, I highly recommend that you register.
You’ll learn new things, meet some terrific people, and spend a couple of days discussing Excel. What could possibly be better than that?
The agenda includes sessions on charts, pivot tables, functions, names and many other topics. Even if you’re familiar with some of the topics, you’ll benefit from attending.
Excel Expert Presenters
The presenters are a very smart and creative bunch, and they’ll almost certainly show you a few tips and techniques that you haven’t tried before.
The Q&A sessions will be an excellent opportunity to discuss any Excel problems that you’ve encountered, and get solutions or suggestions from the presenters and other attendees.
When and Where
Date: April 1-2, 2009
Location: Microsoft London (Cardinal Place)
100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100
The agenda for the two days is outlined at the Excel User Group site and there’s also a Word document with conference details that you can download.
Tell them that Debra sent you, and you’ll get a 25% discount on the free admission! You can use Excel to figure out how much that is! 😉
_____________________________
I’m reviewing Word files and inserting comments for the author. I’d like to see show Word comments in balloons along the sidebar, but Word won’t cooperate, and shows them in a Reviewing Pane, at the bottom of the window. Here’s how to fix the problem
Sometimes there are blank cells in a pivot table’s source data. If you try to count blank cells in Pivot Table source data fields, you might run into a problem.
Here are the steps to follow, to show the count of blanks.