Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Theo uses an Excel worksheet to keep track of reservations in his company. The data consists of only three columns. The first is a person's name, the second the first week number (1-52) of the reservation, and the third the last week number of the reservation. People can be reserved for multiple weeks (i.e., start week is 15 and end week is 19). Theo needs a way to enter a week number and then have a formula determine what name (column A) is associated with that week number. The data is not sorted in any particular order, and the company won't let Theo use a macro to get the result (it has to be a formula).

Theo's situation sounds simple enough, but it is filled with pitfalls when devising a solution. Looking at the potential data (as shown in the following figure) (See Figure 1.) quickly illustrates why this is the case.

** Figure 1.** Potential data for Theo's problem.

Notice that the data (as Theo said) is not in any particular order. Note, as well, that there are some weeks where there are no reservations (such as week 5 or 6), weeks where there are multiple people (such as week 11 or 16), and weeks where there is someone reserved, but the week number doesn't show up in column B or C (such as week 12 or 17).

Before starting to look at potential solutions, let's assume that the week you want to know about is cell E1. You should name this range as Query. This naming, while not strictly necessary, will make understanding the formulas a bit easier.

One potential solution is to add what is commonly referred to as a "helper column." Add the following to cell D2:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Copy the formula down, for as many cells as there are names in the table. (For example, copy it down through cell D10.) When you place a week number in cell E1, then the word "RESERVED" appears to the right of any reservation that involves that week number. It is also easy to see if there are multiple people reserved for that week or if there are no people reserved for that week. You could even apply an AutoFilter and select to only show those records with the word "RESERVED" in column D.

You can, if desired, forego the helper column and consider using conditional formatting to display who is reserved for a desired week. Simply select the names in column A and add a conditional formatting rule that uses the following formula:

=AND(Query>=B2,Query<=C2)

(How you enter conditional formatting rules has been described extensively in other issues of *ExcelTips.*) Set the rule so that it changes the shading (pattern) applied to the cell, and you'll easily be able to see which reservations apply to the week you are interested in.

Finally, you really should consider revising how your data is laid out. You could create a worksheet that has week numbers in column A (1 through 52 or 53) and then place names in column B. If a person was reserved for two weeks, their name would appear in column B twice, once beside each of the two weeks that they reserved.

With your data in this format, you could easily scan the data to see which weeks are available, which are taken, and who they are taken by. If you want to do some sort of lookup, it is easy to use the VLOOKUP function based on the week number, since it is the first column of the data, in sorted order.

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (11077) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Determining a Name for a Week Number.

*Related Tips:*

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

The image included in the tip helps to understand what the tip is talking about. It's a good suggestion to apply for similar tips

Another layout would have the week numbers in Column A, and the names aligned vertically in the remaining columns. In the two rows above the names, put numbers for the start and end of reservations for each person. Then use IF formulae in the table to place a marker (e.g. CHAR(7)) under each persons' name against the appropriate week number. This is easy to read at a glance. Alternatively, conditional formatting could color the relevant cells under each name.

Actually, one more modification could make this alternative format much more viewable. Swapping rows and columns would result in a much narrower table, since the names would be in one wide column and the week numbers would be in 52 or 53 narrow columns. Alternatively, keeping the names as column headers but standing their text up vertically could have the same result.

As Allen points out, it is possible that the current data contains "weeks where there are multiple people (such as week 11 or 16)". But his proposed new data format - "week numbers in column A (1 through 52 or 53) and then place names in column B" - inherently excludes the possibility of more than one worker in a given week. Modifying this new format to include a unique column for each worker would allow for multiple workers in the same week. Instead of inputing names, you would input reservations at the appropriate row/column intersections.