Creating a list of name depending if there on shift that day or night

[Sean Gillan]

Hi there,

Im building a years rota and trying to create a list of names who are on shift by just selecting a date and what shift (dayshift,nightshift,etc). The poupuse for this is to create a signing in sheet for that shift.


The operators names run along the top ( from left to right) .

The date runs down the left hand side ( from top to bottom) .

I need to look down the side and fide the date ive selected (in this case 18-Nov) and look across to find the shift (in this case Nightshift) and put the corisponding names in a list.

I hope ive put enough info here, if not please just ask.


Could anyone help please.

[FDibbins]

You could probably use INDEX/MATCH for this, but I cannot be more detailed without seeing a sample of what you are working with and what you want

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1		Name1	Name2	Name3	Name4	Name5
2	7/21/2014	Day	Night	Night	Day	Day
3	7/22/2014	Day	Night	Night	Day	Day
4	7/23/2014	Off	Night	Off	Day	Day
5	7/24/2014	Off	Off	Off	Day	Day
6	7/25/2014	Night	Off	Day	Day	Off
7
8
9
10	7/22/2014	Night	Name2
11			Name3
12

This array formula** entered in C10:

=IFERROR(INDEX(B$1:F$1,SMALL(IF(INDEX(B$2:F$6,MATCH(A$10,A$2:A$6,0),0)=B$10,COLUMN(B$2:F$6)-COLUMN(B$2)+1),ROWS(C$10:C10))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Sean Gillan]

Hi Toni,

I couldnt get it to work if you could have a look at my file.

You should be able to see a list of names in the setup page.

[Tony Valko]

Sorry, there are restrictions to files I'll download.

They have to be smaller than 50kb and they have to be VBA free.

[Sean Gillan]

Hey Tony,

Ive never used this type of function before could you please explain what each part is doing, then i might be able to sort it out.


=IFERROR(INDEX(B$1:F$1,SMALL(IF(INDEX(B$2:F$6,MATCH(A$10,A$2:A$6,0),0)=B$10,COLUMN(B$2:F$6)-COLUMN(B$2)+1),ROWS(C$10:C10))),"")

Thanks.

[Tony Valko]

Did you enter the formula as an array formula?

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

[Sean Gillan]

I tried that but cant seem to et it to work.

Ive aded a snip of my rota page for you to see.

[Tony Valko]

When you say you can't get it to work what results are you getting? Incorrect results? An error? Something else?

[Sean Gillan]

I put you formula in where i think it goes and it isnt showing any names, all i showwing is ether a blank cell or the formula is visable.

[Tony Valko]

Well, I can't really tell anything from the screencap and your attached file is too big/contains VBA code.

Can you make up a SMALLER sample file without VBA code?

20 rows and maybe 10 columns worth of data is plenty.

[Sean Gillan]

See Atached

[Sean Gillan]

Sorry Wrong File try this one lol

[Tony Valko]

OK, where do you want the results? In column V?

And I guess the criteria is entered in cells F11 = Nightshift for the date H11 = 1 Jan 2014?

[Sean Gillan]

Yeah thats correct, sorry.

This Image is how i intend my Signing in sheet to look.

[Tony Valko]

Here you go.

The entry in cell L18 was the TEXT string 1-Jan.

I changed it to be the DATE value 1/1/2014 formatted as 1-Jan.

[Sean Gillan]

Oh nearly there, not sure what ive done wrong, ive put it in but when i change the Date it all go's blank, it will only display the 1st of Jan??

This is my code as suits my criterea's locations, have i missed something out.

Please Login or Register  to view this content.

Can you work out what ive done wrong.

[Tony Valko]

The formula looks OK.

Did you enter it as an array formula?

[Sean Gillan]

Yeah it works when Nightshift or Dayshift are selected but as soon as i change the date it goes blank,

As im writing this i think its due to the way my dates are entered in to the cells down the left hand side,

example

A1+1
A2+1
A3+1

and so on, do you thinck that would cause this issue.

[Tony Valko]

So the dates are in the range CalenderYear.

Try entering a date in EnterDate (I assume that's a single cell) and then try this formula:

=COUNTIF(CalenderYear,EnterDate)

Results of 0 mean the dates do not match or that EnterDate may be missing from CalenderYear.