Pulling a list from a rota

Mrs T

New Member
Joined
Apr 5, 2024
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hi,
I have a monthly rota that I need to create daily lists from.
The table is simple with staff names and day of the month as the axis. There are several shifts they could be working that are abbreviated in each cell.
I currently create daily sheets as a print out by filtering each dy and cutting and pasting names. I'd love to do this by formula to work smarter.
So what could i use if I want to create a list of everyone with either an E, EH, or EL in column B for example?

1712319983655.png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
One of the easier ways to do this would be to introduce a column heading (here I've called it "Types") and turn on the column Autofilter...then select whichever types you'd like to display using the dropdown selector. The XL2BB add-in doesn't show this autofilter, but see the snapshot below. To turn the filter on, used Data > Filter.
Book1
ABCDEFGHIJKL
1Calendar Start Wk1/1/2023
2MonTueWedThuFriSatSunMonTueWedThu
312/2612/2712/2812/2912/3012/311/11/21/31/41/5
4TypesTypesTypesTypesTypesTypesTypesTypesTypesTypesTypes
5Mickey MouseLHDODOMDLEDOALAL10-6MD
6Minnie MouseDODOELHELEDOMDTEL
7Donald DuckDODODOEDOLEALDODODO
8GoofyEE10-6EDODOLELLHE
9Daisy DuckNDODODOELEDODOELEDO
10PlutoDOLDONNDODODODODON
11LiloELDOLIDODOEEDODODO
12SimbaDOELDODONNDODODOL
13PumbaDONNDODODODOALALALDO
14TimoneELDODODOALDODONNNDO
15GrumpyDOELDOELDODODOALALDODO
16SleepyDODODOLEEDOLEEL
17HappySDODODODONNDODODOS
18DocDODOEEELDODODODOL
19SneezyDOELELDOSDODOSDOSS
20DopeyNNALALALDODOALALALAL
21BashfulDODONNNDODONNDODO
22Snow WhiteSSSDODODODOSSSDO
Sheet2
Cell Formulas
RangeFormula
B2:AJ2B2=B3#
B3:AJ3B3=B1-WEEKDAY(B1,3)+SEQUENCE(,35,0)
Dynamic array formulas.

...with EL, E, and LH selected for Monday 12/26:
Book1
ABCD
1Calendar Start Wk1/1/2023
2MonTueWed
312/2612/2712/28
4TypesTypesTypes
5Mickey MouseLHDODO
8GoofyEE10-6
11LiloELDO
14TimoneELDODO
23
Sheet2
Cell Formulas
RangeFormula
B2:AJ2B2=B3#
B3:AJ3B3=B1-WEEKDAY(B1,3)+SEQUENCE(,35,0)
Dynamic array formulas.

1712328547145.png
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top