Extract Cells from One Sheet and Display Result On Another Sheet According to Criteria

Hello,

I have one sheet called Shifts that has this data:

Please Login or Register  to view this content.

I have another sheet called Shift List where I want to only type the day of the week, and it needs to automatically display the booked people for that day only. Example: I type Wed in the cell, and it automatically displays the three booked people:

Please Login or Register  to view this content.

How do I go about this?

Excel file attached, as requested.

You can use this formula in cell H2 of the Shifts sheet:

=IF(INDEX($B$2:$G$6,ROW()-1,MATCH('Shift List'!$B$1,$B$1:$G$1,0))="Booked",MAX(H$1:H1)+1,"-")

Copy down as required (you might need to change the row parameter, shown in red, to suit your real data).

Then you can use this formula in A2 of the Shift Lists sheet:

=IFERROR(INDEX(Shifts!$A:$A,MATCH(ROWS($1:1),Shifts!$H:$H,0)),"")

Copy down as required.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can use this formula in cell H2 of the Shifts sheet:

=IF(INDEX($B$2:$G$6,ROW()-1,MATCH('Shift List'!$B$1,$B$1:$G$1,0))="Booked",MAX(H$1:H1)+1,"-")

Copy down as required (you might need to change the row parameter, shown in red, to suit your real data).

Hi Pete_UK,

This works perfectly, thank you!

I was surprised to see that formulas were needed in the Shifts sheet.

Is there any way to only have the formulas in the Shift List sheet, and nothing on the Shift sheet?

There are a number of ways that this could be achieved. I personally like this approach of marking the appropriate records in one sheet with the first formula, and then extracting them from those markings (i.e. sequential numbers) in the second sheet, as the formulae are quite short and simple to understand (and thus to maintain in the future if things need to be changed).

Other approaches could include using the AGGREGATE function within INDEX, or array formulae using SMALL and IF, and these would involve a single formula in the second sheet, but I rarely recommend those (plenty of examples exist on the forum).

Hope this helps.

Pete

P.S.: If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

If I run a separate filter, I get to the exact result I am looking for:

=FILTER(I2:I8,J2:J8=I1)

Is there a way to nest these two filter functions to reach the result in one step?