You are here: Home / Archives for May 2008

How to collect and paste multiple items

May 29, 2008 By

The Microsoft Office Clipboard allows you to collect text and graphic items from any number of Office documents or other programs and then paste them into any Office document. Examples are Microsoft Excel, Microsoft Word and Microsoft PowerPoint.

To use the Office Clipboard you must open it in the task pane of an Office program.

  1. On the Excel menu click Edit.
  2. In the Edit menu click Office Clipboard.

    3. offclip.gif

  3. The Office Clipboard will open on the task pane.

    4. offclip1.gif

Now whenever you copy an item, it will appear on the Office Clipboard with the latest item on top.

To copy an item to Excel:

  1. Click on the cell where you want to copy the item.
  2. Click the required item on the Clipboard.

Notes:

  1. You can have up to 24 items collected in the Clipboard. If you copy more than that the oldest item will be dropped automatically.
  2. You can control the Clipboard behavior by using the Options drop down menu at the bottom of the Clipboard.

Applies to: Excel 2003

Filed Under: Data Tagged With: , , , , ,

Random Lookup Revisited

May 25, 2008 By

In a previous post I explained how to perform random lookup based on the VLOOKUP function. The formula was:

=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

I explained how I used the RAND() function to generate values between 1 and 12 for the first parameter of the function (red color). However there is another direct function which generates random numbers within a given range called RANDBETWEEN. To get this function you must install an add-in called the analysis tool pack.

When this function is available then we can replace the previous formula with the new one:

=VLOOKUP(RANDBETWEEN(1,12),P12:Q23,2,FALSE)

This will generate random numbers between 1 and 12.

All other steps will remain as explained in the previous post.

How to install the analysis tool pack

1. In the Tools menu click Add-ins. The Add-in window will open.
2. Click on the checkbox next to the Analysis Tool Pack.
3. Click OK then follow the instructions on the screen (you may be asked to insert MS Excel setup CD).

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: , ,

Random lookup based on VLOOKUP function

May 21, 2008 By

A few months ago I designed a monthly calendar template in Excel. One of my requirements was to pick a proverb randomly from a list and show it in the calendar. This is how I managed to do it:

I first created the list of proverbs as shown below.

xproverbs.gif

This is the calendar template

Calendar

To pick the proverb randomly I placed the VLOOKUP in the cell where I want the proverb to appear.

=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

Now let us dissect the code (Please refer to the post on How to use the VLOOKUP Function for details):

1. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is our lookup value which generated randomly using the RAND() function, and because RAND() returns a fraction I multiplied by 10 to get a whole number. In my case I have 12 items in my table and this is why I added 3 hear to make sure that I will cover all of the table. The INT() function is used to get rid of the fraction to the right of the decimal.

2. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is where the table array is (the list of proverbs).

3. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

The 2 is the column index number. I.e. we are picking our proverb from the second column in the table array.

4. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is the range lookup parameter. “False” means we are looking for an exact match of the lookup value.

Pleas check this post for a different approach to random lookup:

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: , , ,

How to format a cell based on the value of another cell (conditional formatting)

May 18, 2008 By
  1. Select the cells for which you want to add conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. The Conditional Formatting dialogue will open.

    4. xcformatf2.gif

  4. In the first combo box select Formula is.
  5. In the text box next to it write the formula as follows: =$A$1<100. An equal sign followed by the cell reference followed by a comparison or a condition.
  6. Click the format button, the Format Cells dialogue will open.

    7. xcformat.gif

  7. Select the formatting you want to apply when the cell value meets the condition e.g. you can set the font color to red.

In this example, if the value of cell A1 is less than 100, then the color of the selected cells will be red.

Applies to: Excel 2003

Filed Under: Data, Formatting Tagged With: , , , , ,

How to use the VLOOKUP Function

May 15, 2008 By

The VLOOKUP function (short for Vertical Lookup), searches for a value in the first column of a table array and returns the corresponding value in the same row from another column in the table array.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  • Lookup_value:The value to search for in the first column of the table array.
  • Table_array:Two or more columns of data. The values in the first column of table_array are the values searched for the lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase texts are equivalent.
  • Col_index_num:The column number in table_array from which the corresponding value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.
  • Range_lookup:A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If set to “FALSE”, a corresponding value will be returned only if an exact match is found. If set to “TRUE”, the nearest match will be considered if an exact one is not found.

Note:

The values in the first column of table array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.

Let us take an example to clarify the VLOOKUP usage:

In the image below, we have the worksheet Students with two columns of input data. Column A holds the names of the students and column B the abbreviated codes of the states. Since the same state could be repeated for more than one student, then it is more efficient to put the states in a separate table and use VLOOKUP to fetch the names of the states. So in the third column C and in cell C2 we will enter the VLOOKUP function as follows:

=VLOOKUP(B2,States!$A$2:$B$51,2,FALSE)

B2 lookup value , state code
States!$A$2:$B$51 Table array, found in worksheet: States.
2 Col_index_num, column B in worksheet named States which hold the name of the state
FALSE We want to find an exact match

Cells C3 to C10 can be updated with the VLOOKUP function by dragging the fill handle across the cells.

xstudents.gif xstates.gif
Students States

Filed Under: Data, Functions Tagged With: , ,

Function to return the day of the week in text (Name of the day)

May 11, 2008 By

Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return the name of the day itself i.e. Monday, Tuesday…etc.The following code is an attempt to handle this case.


Function WeekdayText(dDate, iRtype)
‘ get the weekday no. from excel bilt-in Weekday function
iwday = WorksheetFunction.Weekday(dDate)
If iRtype = 2 Then
‘ Return the full name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddddd”)
Else
‘ Return the short name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddd”)
End If
End Function

The function accepts two arguments: Date and return type. If the return type is 1, the function will return an abbreviated name of the day i.e. Mon, Tue …. etc. If the return type is 2, the function will return the full name of the day i.e. Monday, Tuesday …. etc.

Use this code to create a user defined function and then you can use it like any other built-in function.

If you are not familiar with visual basic then you can achieve the same result by encapsulating the Weekday function within the Text function, in a cell, as follows:

=TEXT(WEEKDAY(date),”ddd”)

Related Links:

How to create a user-defined function

Applies to: Excel 2003

Filed Under: Date and Time, Formatting, Functions, Visual Basic Tagged With: , , , ,

How to protect or unprotect cells in a worksheet

May 7, 2008 By

In a previous post we talked about how to protect worksheet elements. When you protect a worksheet, by default all its cells will be locked.

If you want to protect or unprotect some cells selectively then do the following:

  1. Select the cells you want to protect / unprotect.
  2. In the main menu click Format.
  3. Click Cells. The Format Cells window will open.
  4. Click the Protection Tab.
  5. Select the Locked check box to protect the cell or clear the check box to unprotect it.

Tip.

In the Protect tab there is a checkbox named Hidden. This checkbox is applicable for cells with formulas.

Select this checkbox to hide formulas from appearing in the formula bar.

Note.

  1. If the Cells command is dimmed when you are trying to protect cells, then parts of the worksheet may already been locked.

Click Tools —> Protection —> Unprotect Sheet.

  1. When you are done, and in order for your cells to be locked then you must protect the worksheet again through the Tools menu.

Filed Under: Security and Protection Tagged With: , ,

How to protect Worksheet elements

May 3, 2008 By

To prevent unauthorized users from performing certain worksheet tasks, like formatting cells, deleting rows or inserting rows, follow these steps:

  1. Switch to the worksheet you want to protect.
  1. On the Tools menu, point to Protection, and then click Protect Sheet.
  2. Type a password for the sheet. Then reconfirm the password when asked for that.
  3. In the list: Allow all users of this worksheet to , check the elements which you want the users to have access to. All unchecked items will be protected.

xprotectsheet.gif

Tip:

You can protect the worksheet without entering a password, but in this case anyone can unprotect it. This is only useful if you want to avoid the worksheet being modified accidentally.

Related Links:

How to protect a workbook with a password

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: , , ,