LOOKUP challange

I have a workbook with two sheets. The 1st sheet is a table (Name Roster) where names are assigned a location and a locker number:

a1: location b1: name c1:locker number

The location is a static number from top to botton(1-1, 1-2, 1-3, etc...) and the names and locker numbers are assigned next to any available (blank) location spot.

The second sheet is a "locker roster", which lists the available range of locker numbers to assign to any given name. (a1:locker b1:name). This just done to have occasional locker rosters print out.

The Name Roster is ever changing since names are constantly deleted or added. But the locations (obviously) remain static. So i use the Locker Roster to find the next available locker to assign to any new name entry.

I want to automate the process of assigning available locker numbers when a new name is entered in the Name Roster table, and vice versa to fill out the name matching the given locker number in the Locker Roster for printing later.

"Name Roster Sheet"
a1:Location b1:Name c1:locker
a2:1-1 b2:John Smith c2: (formula?)

"Locker Roster Sheet"
a1:Locker b1:Name
a2: 1 b2formula?)

In this example I input John Smith in "Name Roster Sheet" b2 and want a drop-down list in c2 of the available locker numbers that i can assign to this new name. In the "Locker Roster Sheet" I want to match the locker number to the name that holds that locker.

I have been struggling with VLOOKUP, ISNA, INDEX and IF formulas to accomplish this task. I have tried the Data Validation tool to aid with the drop-down list but failed also.

What am I doing wrong? What do I miss?

Fried noggin in KS

Hi

What (if any) relationship is there between the locker location and the locker number?

If not, then how many lockers are there (same as the count of locations???) and are the locker numbers sequential starting from 1, or is there some format to the locker numbers?

Perhaps give a larger example showing some corresponding data in both sheets.


rylo

Here's an example of what I mean:

NAME ROSTER

A1: CELL B1: NAME C1: LOCKER#

A2: 1-1 B2: John Smith C2: (Drop-down list)
A3: 1-2 B3: Lois Lane C3: 2
A4: 1-3 B4: Bruce Wayne C4: (Drop-down list)

LOCKER ROSTER

A1: LOCKER B1: NAME

A2: 1 B2: [I](Fill name out when this locker's chosen from drop down list)/I]
A3: 2 B3: Lois Lane
A4: 3 B4: Name

... The purpose is to have a sheet with the table of available locker numbers (1-120) in sequential order. The only thing i need the name matched to the locker number in a table is to print out exclusively to find people's property in the locker room faster. The Location is where the person will be located during the activity away from his/her property.

Originally Posted by rylo

Hi

What (if any) relationship is there between the locker location and the locker number?

If not, then how many lockers are there (same as the count of locations???) and are the locker numbers sequential starting from 1, or is there some format to the locker numbers?

Perhaps give a larger example showing some corresponding data in both sheets.


rylo

Hi

Can you have a look at the attached file and see if this is going in the right direction.


rylo

Wow... I'm so very impressed.

This is exactly what I wanted to do. I am so thankful with you for taking the time to review and help me solve this challenge. I am still reviewing the formulas to try and understand what i had done wrong. Explaining could tutor me better perhaps?

Could you elaborate of what columns D and F in locker sheet serve for?

I can give you my email upon request.

Thanks...

Originally Posted by rylo

Hi

Can you have a look at the attached file and see if this is going in the right direction.


rylo

OK, here goes.

Starting with sheet LOCKER.

1) B2: this formula searches for an appearance of the locker number in A2 on Name column C using the MATCH function. If it doesn't appear (by giving an error), then it gives a blank. If it does appear, then it uses a combination of INDEX and MATCH to bring back the name from the Names sheet.

2) The formula is column D bring back a row number when there is no name allocated to a locker. This is used in the formulas in column F

3) Column F is where a list of the unused locker numbers is calculated.
COUNT(D:D,"<>"): brings back a count of numbers in column D
ROW()>COUNT(D:D,"<>"): a test to see if the current row number is > than the count of numbers in column D. If it is the IF statement will show a blank.
INDEX(A:A,SMALL(D:D,ROW())):
row() brings back the current row number. This increments as you go down the column
SMALL is a function that returns the 1st smallest, 2nd smallest number etc. So by using the row number it brings back the nth smallest number in column D.
INDEX then uses this row number to bring back the entry from column A - the unused locker number.

4) There is a defined name LockNos with the formula
=OFFSET(Locker!$F$1,0,0,COUNT(Locker!$F:$F),1)
This is a dynamic name that takes it length from the count of numbers in column F. Have a look at the help file for OFFSET to see how this formula works.

Sheet NAME

1) Column C has a data validation that is linked to the defined name LockNos. Every time you make a selection, the formulas described above will recalculate a list of unused locker numbers and the dynamic name LockNos will be updated with the unused list ready for the next selection. This works both ways - using and releasing a number.


rylo