Restricting Editing to Individual Cells in Excel 2007

What if you only want to protect a single, or several single cells, in an entire Excel worksheet and allow users to edit or input data in the rest of the sheet?

Let’s say you have a worksheet like this:

protect-4

You would like your department members to edit the “data” in the black text cells, but you want to protect the “data” in the red text boxes from accidental changes.

First, you need to unprotect the entire worksheet. That sounds counterintuitive.
But Excel protects all cells in the worksheet by default whenever you set protections, so we need to turn that off in order to only protect individual cells.

To do this:

Click on the worksheet selection cell at the top-right corner of the worksheet to select the entire worksheet protect-5
Click the down-arrow on the Format icon on the Cells tab protect-1      
Click on Format Cells…
This will display the Custom Lists dialog.
Uncheck the “Locked” checkbox, then click “OK”.protect-6 		 protect-2
Now select the cell or cells you wish to protect.
(Select multiple cells by holding down the SHIFT key for contiguous cells or the CTRL key for non-contiguous cells.)		 protect-7
Click the down-arrow on the Format icon on the Cells tab again and select “Lock Cell” protect-1
In the above example, we have now locked 3 cells.Now, Click on the Format icon again and this time click on “Protect Sheet” and uncheck “Select locked cells“. Then click “OK”

This prevents users from selecting – and editing – the cells that we locked in the previous step.
Be sure to leave “Select unlocked cells” checked – or your users will not be able to edit any cells at all!

protect-8

 protect-2

Save your worksheet and close it.

Now anyone who opens the worksheet will only be able to select and edit those cells that are not locked.

 

Google Calendar Quick Start

(Need Info on Initiating Calendar Process – Unavailable at this time but will go here!)

Importing Schedules from Oracle

Exporting Your Oracle Calendar Data
cal-8 
  • Open your oracle calendar
  • File > Export Data 
  • Select “iCalendar File“
  • Click “Continue”
 cal-9  

  • Under Export:
  • Check Meetings, Day Events, Daily Notes
    Check Tasks if you use this feature
  • Under Period:
    Select All if you want all past and future information exported
    Select Range and enter date information if you want to export a specific time frame
    Select Custom if     you want to only delete a snapshot of past and future events
  • If you administer someone else’s calendar:
    Enter that person’s name under People/Resources
    Select their listing and click the green checkmark
    Their name should be added to the text entry space along with yours
  • Browse to where you want to save the file
  • Click”OK”
  • If you have not previously exported your calendar, Oracle will also ask you for a filename
  • Click Save

 

Importing the Oracle Data to Google
 cal-10  

  • Log into your Google account
  • Display your Calendar
  • Click on “Add” at the bottom of the left-hand side of the Google Calendar
  • Click on “Import calendar”

cal-11

 

  • Browse to your file and click “Import”
 

  • You will get a message about how many “events” were processed and how many were successfully imported
 cal-12
 

  • If you receive errors, you can either browse your new calendar to find those that are missing and add them by hand
    or you can try exporting to a different format (vCal for example) and trying that.
    Some entries may not import in any format.
  • Possible errors include:
    • Too large a file
      The recommended file size when importing files into Google Calendar is 1MB or less.
      If your file is larger than 1MB, try importing events from a smaller range of time. 
    • “Processed 0 events” message
      This error often occurs if you have accidentally clicked the “Import” button more than once
      You events may have been imported on the first click – but the file would be empty on the second click.
      Check youe calendar before trying another format to make sure the events are not there. 
  • Once imported your calendar will display the events noted in your Oracle Calendar

 

Adding Events

cal-1 cal-2
  • Click the Create Event link on the upper-left of Google Calendar screen
  • Fill in event details: date, start time, end time, location, and description
  • Set your desired notification setting: email, text message, or a pop-up message on your computer (only works if Google Calendar is currently open)
  • Click Save  

Or Quick Add allows you to enter basic event details, and interprets how to display your event on the calendar.

  • Click the desired time slot in the calendar
  • Drag your mouse to determine length of event
  • Enter a title
  • Add more details if you wish
  • Click Create Event

 

Adding Calendars

 cal-3  

  • Click the Add button on the left of the calendar
  •  Select Create a new calendar
  • Enter Calendar Details: (name, description, time zone, and email addresses of people you need to share it with)
  • Click Create Calendar

If you will be working with multiple calendars, it may be helpful to assign colors to them. To do this:

  • Click the down-arrow next to a calendar’s name in the calendar list on the left
  • Select a color from the menu

Sharing Your Calendar

 cal-5 You may want to share you personal calendar with friends and family; or you may need to share a group or departmental calendar. Or, you may want to make a calendar public. You can do all of these things in Google Calendar.

  • To display the Calendar settings dialog, click the down-arrow next to the Calendar’s name
  • To work on sharing settings, click on “Share this calendar“

To make the Calendar public:

  • Click on “Make this calendar public“
 To share the calendar with specific individuals:

  • Enter the email address of the user with whom you want to share the calendar
  • Select the desired level of permission from the drop-down menu
  • Click Add Person
  • The person you selected will receive an email invitation to view your calendar

Permission Options:

  • See only free/busy (hide details)
  • See all event details
  • Make changes to events (individuals can make changes to events)
  • Make changes AND manage sharing (individuals can share the calendar with their groups)

 
cal-6

 

Sending Invitations

 cal-7
  • Click on the event, or create a new event
  • A pop-up window will appear
  • Click the edit event details link at the bottom of this pop-up
    OR 
  • Double click the event in the calendar
  • In the Guests box on the right, enter the email addresses of the people you’re inviting
    (I presume there will be something in here to access the colby list?)
  • Select the type of access you would  like your guests to have: (whether they can invite more people to your event, view who else has been invited)
  • Click Save 
  • Guests can respond to the invitation and have the option to leave comments 

 

Staying Connected

You can access Google Calendar on your mobile device.

  • Enter http://www.google.com/calendar in your phone’s web browser
  • You can view your agenda, add events, and invite others

You can also stay connected via your mobile device by setting up SMS reminders for your events.
With this feature enabled, Google will send you a text message (you have control over how far in advance) with the name, time, and location of an event.
Click here to learn about setting up and enabling SMS messaging for your Google Calendar.

   

Batch Upload for WordPress

Save Time and Energy Uploading Multiple Files Simultaneously

Much of the time, users create a single post or page in WordPress and upload any images or documents at the same time.

However, there are times when a user might want to upload all their images or documents first, and then insert them as they create pages and galleries over time.

This is easy to do in WordPress.

1. Assemble all your files in a single folder on your computer.
2. Click on “Add New” under the Media tab on your WordPress site dashboard
3. Click on “Select Files” in the “Upload New Media” screen
4. When the Browse box is displayed, go to the folder that has all the files in it and drag your cursor to select all the files in the folder
upload-1
5. Click “Open“. WordPress will upload each file in turn.
upload-3
6. When it completes the last file, Click “Save all changes“. All your files will be uploaded.

Concatenating Text Columns in Excel

Adding “@colby.edu” to a List of Simple Usernames

What do you do if you need to send out a mailing and all you can have is an excel list of simple usernames: no @ sign, no “colby.edu”. 

Will you have to edit each one by hand? No!

These easy steps will save you a ton of work:

1. You will need 4 empty columns to the LEFT of the column of usernames.
 It should look something like this:		 email-2
2. Insert the “@“ sign in the top cell of the second column – then copy and paste it into all the remaining cells of that column. email-3
 
email-4
3. Now enter “colby.edu” into the top cell of the third columns – then copy and paste that text into all the remaining cells in the column. email-5
 
email-6
4. Enter the function: =CONCATENATE(A2,B2,C2) into the top cell of the fourth column and press Enter.
Note: You will need to adjust the cell identifiers (A2, etc) to match the positioning of the data in your spreadsheet.		 email-7
5. Copy and paste the function into the remaining cells. email-8
 6.  Now you need to copy the VALUES of the function in the last column into a new column that you will use for the mail merge.WHY?Since the text in the last column is actually the result of a FUNCTION, it:

1. May not insert correctly into your mail merge document. It might carry over as the “=CONCATENTATE” formula rather than the email address itself.

2. Will revert to an error if you delete either of the two work columns you created to create the email addresses

To Solve This:

Just COPY all the newly created email addresses and PASTE them into a new column using the Paste Values option under Past icon at the top left of the ribbon.

You can now delete all 3 extra columns you created since nothing is now pointing to any of them. And you will have usable email addresses.

  email-9

Using Word’s Document Map View

Are you tired of having to scroll up and down through a very long Word file to find where you want to edit some text? Word’s Document Map view is for you!

Document View in Word will display a list of all the headings in your document down the left-hand side of your document. You can easily move up and down through your document by clicking on headings in this display. To use this feature, headings need to be formatted using the built-in heading styles (Heading 1, Heading 2, etc.).

To display the Document Map:

  • View > Document Map

docmap-1

  • This will display the document map along the left side of your document

docmap-2

To Display Thumbnails Rather than Headings:

  • Click on the down-arrow next to “Document Map” at the top of the view and select “Thumbnails“

docmap-4

  • Word will then display thumbnails of the pages

docmap-5

If you wish to use the outline form to rearrange sections of text in your document, you must use Outline View.

Hyperlinking Between Worksheets in Excel

You probably create links to web sites in Excel all the time. But did you know that you can create a hyperlink to different worksheets in the same workbook? Internal hyperlinks will save you time and – if you create an “Overview” sheet at the front of workbooks you want to share with team members – enable users to quickly link to the exact place they need to go without hunting through multiple worksheets.

Creating an Internal Hyperlink

  • Launch Excel and open the workbook to which you want to add an internal hyperlink.
    For demonstration purposes, we will have a workbook with four worksheets: (Resources, Team Members, Budget and an Overview page like this:

hlink-1

  • Select the cell you want to make your hyperlink.

hlink-2

  • Insert > Hyperlink from the Ribbon
    Click Place In This Document on the left
    Specify the text you want the linked cell to display in the Text To Display text box
    (Go to Worksheet)
    Select a worksheet
    Click OK

hlink-4

  • Your sheet will now look like this:

hlink-5

  • Clicking on the link will take you to the top-left of the Resources worksheet.
    If you wanted to go to a specific cell on that worksheet, such as D12, you can specify that cell in the hyperlink dialog:

hlink-6

  • Now you sheet would look like this:

hlink-7

  • Clicking on the cell link will take you to that exact cell on the referenced worksheet
  • Hyperlinks can also be used to quickly move around a single sheet which has many entries and categories

Embedding Video in PowePoint 2007 (PC) or 2008 (Mac)

We all know we can link to a video that is sitting somewhere on the Internet from PowerPoint. But what do we do when: 1) An Internet connection is not available (or fast enough) where we are presenting or 2) The video we want has unexpectedly been moved or deleted or 3) The video is one we created and is not on the Internet.

Not a problem! Just follow these steps:

(Instructions for PC ~ Instructions for Mac ~ Presentation Issues)

FOR PCs

For PCs ONLY – go here for Mac instructions.

1 Create the slide from which you want to play the video. I am calling this slide “An Example of Stop-Action Video“.
  video-1
2 Make sure the “Developer” tab is being displayed.
Click on the Windows “Button“, then click on PowerPoint Options.
  video-3
  video-2
  Check the box in front of “Show Developer Tab“. Click OK.
3 Click on the Developer Tab
  video-4
4 Click on “More Tools“.
Scroll through the list and select the Windows Media Player.
This will give you a mini-player right inside PowerPoint that allows you to start, stop and replay the video.
Click OK.
  video-5
5 Your cursor will now look like a cross-hair on the slide.
While holding the LEFT mouse button down, draw a rectangle of the size you want your player to be. When you release the LEFT mouse button, your slide will look like this:
  video-6
  If you accidentally release the button before you have drawn the rectangle, just start again at step 4.
6 Once you h ave the player inserted, Right-Click on the player. Click on “Properties“.
  video-7
7 At the top of the Properties input dialog, click on the “…” button in the “Custom” row.
  video-8
8 Browse to find your video file in the Windows Media Player Properties dialog.
You can also set other variables in this box. For the purposes of this tutorial, we are taking all default settings.
  video-9
  Click OK.
  Click on the small “x” at top-right to close the Properties input dialog.
Your slide will now automatically play the embedded video when it is displayed.
Click here to save a short, 3-slide PowerPoint show that illustrates this task. Save the file to your computer and then double click on it to run the show.

 

FOR Macs

Presentation Issues

There are many issues that you can run into when presenting PowerPoint shows with embedded or linked files. These include:

  • Your presentation can’t find the file
  • Your presentation slows down significantly
  • Your video won’t play

These almost always relate to:

  • What version of Windows or Mac OS you are running
  • Where you linked files physically reside
  • Whether you are using the same machine you created the PowerPoint on to display the presentation

The permutations of factors makes it impossible to list specific solutions for any situation. However, here are some hints that MicroSoft offers:

  1. If possible, always run your presentation from the SAME machine you create it on
    This eliminates almost ALL issues
  2. If you have to use another machine, be sure you TEST the presentation before you show it
    This gives you plenty of time to troubleshoot issues before you go “live”
  3. Always LINK files that are larger than 50MB
  4. EMBED files that are 50MB or smaller
  5. If you have to present on a “stranger” machine (such as equipment in a convention hall) PACKAGE your presentation for CD in order to keep all linked files where your presentation expects to find them