Rota cycle

[Steve]

Hi,

I have a rota in a sheet. It is 7 columns wide (Sun-Mon) and 30 rows (weeks)
deep. When completed, a rota may cover one week; thirty weeks or any
combination in between.

I need to be able to increment the rota either 1 day forward at a time or 1
day backwards at a time. Example, moving a two week rota forward, day 1
becomes day 2, day two becomes day 3 ... day 14 becomes day 1.

Moving a value to a 'holder' then moving other values one-by-one is too
slow. Any suggestions?

Steve

 

[Guest]

Bit hard to be sure what to do without seeing the spreadsheet including
formulas if any.
However, instead of copying and pasteing, is it possible to 'Cut' the last
row of data and use 'Insert the cut cells' into the first row which moves
everything down one row? If you have any formulas with references to other
cells then you would have to check what they do.

Regards,
OssieMac

 

[Steve]

That's an option I didn't consider. Hmm... Data are sitting in a named
range. There are formulae surrounding the range and formulae in other areas
of the sheet in the same rows and columns. There are links from a lot of
other sheets. The result could become catastrophic! However, the general
principal should work if done in an array. Unless someone knows something
about arrays I don't (which would be easy) moving things around in a 2D
array would take some complex code but, working through your idea moved my
thought processes past their block. Pseudo-code:

- Move relevant values into a 1D array

- Take either first or last value and move to opposite end of array (however
that is done)

- Turn the array into 2D array

- Paste 2D array to sheet


My array skills are rusty (to say the least) but I should be able to achieve
this with a bit of reading and determination.

Thank you,

Steve