Formula for moving rows of cells??

[KeirL]

Hi everyone
I'm new to Excel and this forum and don't even know if what I'm trying to do is achieveable but here goes

I'm trying to develop a spreadsheet which acts as a basic 'tasks' rosta for teams of cleaners in a large hotel - There are 5 teams and five areas that each team is assigned to. The teams could find themselves in any area (the areas are Bedrooms, Bathrooms, Kitchens, Recpetion Rooms and Gardens) but there is no pattern of rotation and thus can vary.

So I have cell A2 showing todays date using =TODAY()
and I like to have a 4 week view so cells A3 through to A32 use =TODAY()+1 and =TODAY()+2 etc etc to give me a list of dates in column A with todays date always in row 2

In column B I show the day of the week by using =TEXT(A2,"ddd") for each cell in column A

So this is where I now run out of talent.......

Coloumns C through to G are assigned to each Team (Team 1 through to Team 5) and I'm simply putting in text on each of the days in the 4 week view that says 'Bedrooms' or 'Bathrooms' etc so each team can review the spreadsheet to find their area of work. I can populate the entire 4 week view so everyone can look ahead at what their doing etc

It's important that the current day is on the top of list of dates and this is working fine as when the spreadsheet is opened each morning the =TODAY() formula is updated in column A and Column B is also updated to reflect the correct day of the week - however, there is nothing in place to 'move' the contents of columns C to G up a row so that it still aligns with the correct day.

Is there anything that I can do that will automatically ensure that a row of cells will (somehow associated) with a date vaule in a particular cell so that if that date vaule moves to a new row then these cells will get copied to that same row?

I've attached a copy of the spreadsheet if that would make things clearer as it contains no sensitive information.

kind regards and help help greatly appreciated
Keir

[DBY]

Hello Keir
If each team is allocated 1 task on a particular day throughout the month (eg. Team 1 does Bedrooms every Friday) then you could perahps create an allocation table and use formulas to match the day of the week to a team's task. Take a look at my attachment to see if it does what your'e looking for.

Hope this helps.
DBY

[KeirL]

Hi
Thanks for the input and it's looking close to what I need - certainly it keeps the tasks aligned to the appropriate date.
In answer to your question then unfortunately the teams do not have a regular weekly repeat on the taks and can vary dramatically included some days of no taks at all.

I was thinking of a more physical approach using some kind of trigger (change of date would be perfect) that would delete cells C2:G2 and shift up the cells below - but perhaps I'm over-simplifying things

Thanks again
Keir

[DBY]

Hello
I had a feeling it wouldn't be that simple. The only thing I can think of at the moment without resorting to VBA, of which my skills are limited, is to use a full year sheet to enter your team allocations (Rosta tab) and use the Calendar as a 4 week View, if I'm right in thinking that's its purpose. See my second attachment.

DBY

[KeirL]

Hi
That'll do nicely - Thank you so much.
I can easily work using the Rosta worksheet and populate that with the details. It'll also give me a bit of historic data I can review if necessary to see how the work has been distributed in the months gone by.

Thanks very much again - this is great!

[Cutter]

@ KeirL

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.