Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Determining a Name for a Week Number

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.

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:

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Juan    05 Jul 2014, 15:39
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
Pete Menhennet    05 Dec 2011, 23:03
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.
Erik Oberg    03 Dec 2011, 13:06
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.
Erik Oberg    03 Dec 2011, 12:51
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.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.