Do not know how to

[The_Ball]

Good day friends,

I have an issue that I think should be simple but I can not determine what functions I should use. In a smaller version of my much larger schedule I have a list of employee names (column a) and column b contains times they are scheduled. Some of these cells are empty in column b due to the fact they are not scheduled. In my data validation cell, I only want the names of column a to show up if indeed there is a scheduled time listed in column b. Could you please write me a sample code that would perform this function?

 

[FDibbins]

Hi, welcome to the board

have you considered just applying filters?

 

[WarfritLive]

You mean like =IF(B1="",A1,"")?

 

[FDibbins]

No, there is a built-in filter feature in excel

1. click on the 2st heading
2. On the HOME tab/Editing/Sort and Filter
3. Select Filter

You will now see that each column has a small "drop-down" triangle. If you click on the 1 in the column you want to filter, uncheck "Blanks" and click OK. You should be left with only the filled cells
If you play around a bit, you will see how useful this can be for all sorts of displays and filters

 

[The_Ball]

Hi, welcome to the board

have you considered just applying filters?

Thank you for your response. Ultimately, this formula is going to be on a different sheet and there will be a sheet for each day of the week that gets data from the "master schedule" The "IF" argument will most definitely be a part of the formula, but from my research, so will the index, offset, Match, and possibly others as well.

I can visualize what i want in my head, but it is a series of nested functions and I always get confused as to the format of the nests, (),[], and such.

The data validation should be duplicated on each sheet for a number of in store positions that need to be filled. but should only show the names of employees of employees working that day. For the Monday sheet, the data validation fields will look in column A for the name of the employee and then check to see if they are on the schedule for that day in column B. The formula duplicated on the Tuesday sheet will do the same formula but will check Column C to see if there is a scheduled time and so forth for each day.

So, if i have a list of positions to fill, say phones, pizza maker, oven tender, I want to see a list of only those people who are scheduled to choose from who can fill that position. The help I need is writing that formula with the correct syntax.

Thank you for your help my friends

 

[The_Ball]

Thank you for your response. Ultimately, this formula is going to be on a different sheet and there will be a sheet for each day of the week that gets data from the "master schedule" The "IF" argument will most definitely be a part of the formula, but from my research, so will the index, offset, Match, and possibly others as well.

I can visualize what i want in my head, but it is a series of nested functions and I always get confused as to the format of the nests, (),[], and such.

The data validation should be duplicated on each sheet for a number of in store positions that need to be filled. but should only show the names of employees of employees working that day. For the Monday sheet, the data validation fields will look in column A for the name of the employee and then check to see if they are on the schedule for that day in column B. The formula duplicated on the Tuesday sheet will do the same formula but will check Column C to see if there is a scheduled time and so forth for each day.

So, if i have a list of positions to fill, say phones, pizza maker, oven tender, I want to see a list of only those people who are scheduled to choose from who can fill that position. The help I need is writing that formula with the correct syntax.

Thank you for your help my friends

 

[FDibbins]

The "IF" argument will most definitely be a part of the formula

Not necessarily.

Can you show a sample of what you are working with, and what you want?

Assuming your data looks at least something like this...

	A​	B​
1​	Name	Day
2​	aa	Mon
3​	bb	Mon
4​	cc	Mon
5​	dd	Tue
6​	aa	Tue
7​	bb	Tue
8​	cc	Wed
9​	dd	Wed
10​	aa	Wed
11​	bb	Thu
12​	cc	Thu
13​	dd	Thu
14​	aa	Fri
15​	bb	Fri
16​	cc	Fri
17​	dd	Sat

Then on sheet Mon (or whatever you use for your days)...

	A​	B​
1​	Name	Mon
2​	aa
3​	bb
4​	cc

A2=IFERROR(INDEX(Master!A:A,SMALL(IF(Master!$B$2:$B$17=$B$1,ROW(Master!$A$2:$A$17)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER

 

[The_Ball]

Will try that my friend when I get near my computer. Thank you

 

[The_Ball]

Thank you again for your help. While that formula didn't quite do what I envisioned, it set me on the proper path to something that did. Thank you Ford

 

[FDibbins]

Good stuff, so what did you end up with?