Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining a Name for a Week Number.

Determining a Name for a Week Number

by Allen Wyatt
(last updated July 8, 2014)

1

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) quickly illustrates why this is the case. (See Figure 1.)

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. Further, name the range that contains people's names (in this example, cells A2:A10) as ResNames, the starting weeks (B2:B10) as StartWeeks, and the ending weeks (C2:C10) as EndWeeks. Finally, define a name for the entire table (A2:C10), such as MyData. This naming, while not strictly necessary, will make understanding the formulas much 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.

Another approach is to use an array formula. Select a few more cells than the number of overlapping reservations you expect, and then enter the following into those cells by pressing Ctrl+Shift+Enter:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)*(EndWeeks>=Query)*(ROW(ResNames)),ROW()-1)-1),"")

When picking the number of cells you want this array formula to occupy, look at, for instance, the number of people that may be reserved over week 11. In the example shown in this tip, it is 2 people. Select more than that number of cells and then put the array formula in those cells. If you expect that you might have 20 people potentially booked for the same week, then you'll want to pick a larger number of cells, such as 20 or 30. Just select the cells, put the formula in the Formula bar, and then press Ctrl+Shift+Enter.

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 (11078) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel 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

Changing Text Case

Word provides a built-in shortcut to change the case of a text selection. Understanding how that shortcut works (and the ...

Discover More

Centering Your Worksheet

Got just a small amount of worksheet data to print out? It might look better on the printed page if you center it. Excel ...

Discover More

Changing the Legend Location

When you create a chart, Sheets helpfully adds a legend to better explain the data that is in the chart. If you don't want ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Dealing with Long Formulas

If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to do is ...

Discover More

Filling Cells with Decreasing Cell References

AutoFill is a great feature. It can detect patterns and adjust cell contents as you drag a selection on-screen. It doesn't ...

Discover More

Calculating an IRR with Varying Interest Rates

You might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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. Maximum image size is 6Mpixels. 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 two less than 2?

2014-07-08 14:03:13

Bryan

I'm not sure I agree with changing the data structure to the one recommended in the article. If it were impossible for the room to be double booked, then the new structure is actually better, but as written it appears that multiple reservations can be made, and the new structure can't handle that.

One possible structure that could work is a two-column structure with Name in the first column and Week as the second column. Someone who reserved the room for 3 weeks would have 3 records. This does not violate 3rd normal form like the suggestion in the article, and makes it really easy to see who has week X reserved, or how many people have week X reserved. In addition, the data is easier to pivot, to create a chart of who has the room each day.

The downside to this structure is it would be a little foreign to some people, and it would create a LOT more rows, which could cause a headache for data integrity and cleanup.

Ultimately, depending on what the usage is, I like the original structure better. The formulas are a little bit more complex, and pivoting won't work, but you gain simplicity and ease of data entry.

I feel sorry for someone whose boss won't let them use VBA to solve a problem (even though I'm not sure it's needed here anyway) :(


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.