## Archive for July, 2011

### Excel: Keeping an Eye on Data with the Camera Tool

In a recent post on this blog, I wrote about customizing the Ribbon in Excel 2010; and I used the Camera Tool as an example of an item you might add as part of that customization. The Camera Tool can be used to watch in one workbook or worksheet values in another workbook or worksheet.

For my simple example here I will use my calculation of the number of days until the next Super Bowl as the information I want to keep watching. In my AllThingsFootball.xlsx workbook I have placed the date of the big game in cell A1: 2/5/2012 and the formula for the current date in cell B1: =today(). Then I can place the formula to subtract the two: =A1-B1 in cell C1. With the formatting for cell C1 set to Number, I can see the number of days from now to the end of the season. Each day the value in B1 will update to the current date, and the difference will automatically update in cell C1.

Now, I need to get busy and do some work in my workbook called ImportantStuff.xlsx, but I also want to continue monitoring the number of days until the championship game. I can use the Camera Tool to capture a live picture of the calculation and place it in ImportantStuff.xlsx. It creates a link to the original cells.

### it2go – Episode 18 – The Cyberinfrastructure Building

On this week’s episode we’re talking about our new home at the Cyberinfrastructure building. We’re moving in in a couple of weeks, and so we decided to tell you all about it. We also manage to get some Illustrator talk in.

We had some tech problems the last couple of weeks, but we’re back this week. We’re off next week, and hopefully back the week after. Thanks for sticking with us!

it2go – Episode 18

### Make Data Entry Easier in Access with Lookup Fields

A lookup field can make data entry much simpler and allows the user to choose from a list of values. There are two types of lookup fields: (1) a lookup list pulls data from an existing table or query (based on a one-to-many relationship) and (2) a value list uses a current list of values that you add when you create the field in a table. In addition, you can store multiple values in a field. Here the focus will be on creating a value list lookup in a table that could look something like this:

### Make multiple calendar entries private in Outlook

Before starting my job with IT Training, I had my personal and professional calendar organized using a Palm Pilot m125. I bought it for myself in 2001, and have been using it for everything since then. Since replacements were cheap and easy to find on eBay, and I had my data synced with my office computer, I saw no immediate reason to change. The fact that I didn’t need to share my calendar with anyone made a big difference.

Here at IT Training, we use Microsoft Outlook and the Exchange server to share calendar information. I decided to try importing my Palm Pilot calendar into Outlook – and much to my surprise, it actually worked. However, in a way it worked too well. I wasn’t necessarily pleased to see every last activity in my life for the past 10 years show up in Outlook! Ok, I thought, I wouldn’t mind holding on to this information (you never know when it might come in handy) – but I’d prefer to keep it private.  The question became: Can I make multiple items “private” at the same time, or am I stuck opening each and every one and making them private in turn? I just couldn’t commit to doing this 5000 times in a row.  There had to be an easier way. Read the rest of “Make multiple calendar entries private in Outlook” »

### Tips for Populating Cells in Excel Using Auto Fill

Recently an Excel question came to me regarding using the fill handle to populate dates. The fill handle is a tiny dark square located in the lower right corner of a selected or active cell; when you place the cursor over this small square, it will take the shape of a small plus sign:

Not only can you copy formulas by dragging this fill handle, but you can quickly populate cells with a series of numbers or dates. The specific question was: How can you skip weekend dates and populate a contiguous range of cells with weekdays only? No problem! When you stop dragging the fill handle across a range of cells, an Auto Fill Options tag will appear. When you click on this tag, you will see:

All you have to do is choose Fill Weekdays.  You can choose to  fill cells with days, weekdays, months, or years, or fill the cells with formatting only. Remember that the date format that you begin with will be what Excel will use for the entire range. Notice also that you can choose to copy cells rather than using the Fill Series option.

Something else that you can do with this fill handle is to populate a range of cells with a particular numbering pattern. For example, what if you only wanted the contents of cells to be listed in this order: 1, 3, 5, 7, etc. Or what if you wanted to use a pattern of increased dollar amounts, such as \$5, \$10, \$15, etc.?  All you have to do is to select the first few cells (two or more) that will tell Excel what the given pattern is, and then drag the fill handle across the desired range. Also, you can drag this fill handle any direction. If you drag up or to the left, the series will decrease rather than increase.