Formula reference decrementing when data moved.

[OZZIEUNITED]

I have created a spreadsheet that is quite disjointed and very basic for my limited excel knowledge. I have created all of this from scratch using google and forums such as this to create it. I am coming across an issue where the cells that I am referencing have data shifted in them. The formula that reference these cells is decrementing a value when the data is shifted and I want it to stay the same. I have a mixture of recorded macros as well as typed formulas. The data in the RED,BLUE and WHITE tabs is first randomised using the RAND function and a some code that I created from scratch, and then copied and pasted back into the cells in the randomised order. Sometimes this doesn't always start at the top cell and therefore creates the issue that I am having.

I will link the sheets so that people can see what I have created. If people have a better way to accomplish what I am trying to achieve I would much appreciate it. In essence it is a roster for shiftwork every day throughout a week. Please ask as many questions as necessary and I will try to explain anything that doesn't make sense in what I have created.

I have also posted this on http://stackoverflow.com/questions/2...hen-data-moved