Creating a dropdown list that returns a defined numerous columns of data

[kapadons]

	a	b	c	d
1	monday	tuesday	wednesday	thurs
2	ted	bill	frank	mary
3	frank	will	ted	tony
4	bill	bob	bob	ted

<tbody>
</tbody>

Sheet 2








sheet 1

	a	b
1
2
3
4

<tbody>
</tbody>

We use excel for sceduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers are expierenced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day. For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.

I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.

I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work becuase I would like the names that are being defined on a separate sheet.

Thank you for your time and any help would be greatly appreciated.

 

[Jerry Sullivan]

Hi kapadons,

I don't know why that didn't work for you.

If you define a range named "monday" that consists of the range that holds several names, you should be able to use data validation to have that as a drop down list with all the named in that range. This works even if the name range is on a different sheet (although it needs to be in the same workbook).

When you setup your data vaidation make these entries:
Allow: List
Source: =monday
Check In-cell dropdown.

Note there are no quotes or parentheses in =monday

EDIT: Rereading your post, I think you were trying to enter =monday directly into the cell.
Instead of that, you need to select Data Validation from the Ribbon and follow the prompts.