Rota in Excel - Formula for creating daily overview based on shifts times

**AliGW** · 04-11-2019, 07:30 AM

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**pokerjam** · 04-11-2019, 08:05 AM

Hi Ali

Many thanks for the prompt response.

I have slimmed down the attached rota to show a 2 week block on the first tab with 20 staff on it. On the second tab is the format I would like the daily sheet to appear, columns are headed with the shift time and then the name of that person/s working that particular shift. I am only looking for those particular shift times as headed on tab 2, other information you will see such as "OFF", "H" or "R#" can be ignored.

As requested on tab 2 I have created a mock up for 06/05/19.

I look forward to response, and thanks again for your help.

Jamie (pokerjam)

**torachan** · 04-14-2019, 03:38 AM

Try the attached.
There is a combobox on "Daily" that is populated from the dates on "Rota"
Right click "Daily" tab on small menu select "View Code" (this open the code window of sheet).
The code is almost self explanatory and expandandable by adjusting parameters to suit.
There is no sheet formula to get corrupted.
torachan.