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), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Determining a Name for a Week Number.

# Determining a Name for a Week Number

by Allen Wyatt
(last updated August 4, 2014)

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.

##### Author Bio

Allen Wyatt

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. ...

##### MORE FROM ALLEN

Tab stops allow you to modify the horizontal position at which text is positioned on a line. Word allows you to preface the ...

Discover More

Can't Place Merge Field in Header Of a Catalog Merge Document

Word can perform several different types of mail merge operations, and the type you choose can affect how you are able to use ...

Discover More

If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in Excel. ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

Using a Numeric Portion of a Cell in a Formula

If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric portion of ...

Discover More

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need to ...

Discover More

Counting Employees in Classes

Excel is very good at counting things, even when those things need to meet specific criteria. This tip shows how you can do a ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 0?

2014-07-05 15:39:08

Juan

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

2011-12-05 23:03:43

Pete Menhennet

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.

2011-12-03 13:06:04

Erik Oberg

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.

2011-12-03 12:51:54

Erik Oberg

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.

##### This Site

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.