Adding formatting to cells in a linked spreadsheet.

Hi everyone,

As the title says I am having some trouble with adding colour to the cells on a spread sheet I have.
To give some background: our company keeps a large spread sheet which has all the data about our clients on it. Recently our Admin team asked if they could have their own copy of this spread sheet so that they can use it to track certain things (auditing etc). They wanted to be able to add colours to the spreadsheet to identify different things but of course they aren't allowed to do this on the master spreadsheet as the data is very important and the company cannot risk it being edited by accident. Also, the spreadsheet changes regularly as new clients are added so we decided the best thing to do was link the data to a new spread sheet and then they could do whatever they needed to without causing problems. All this worked fine and solved everyone's problems. Unfortunately we found a flaw, any colour or formatting they added to the 'slave' sheet is connected to the cells not the data. I will give an example...

On the master (and therefore also the slave) cell A1 contains the name Joe Allen. On the slave sheet the admin team colour this cell red. A new client now comes and the name Sue Abbott is now inserted into cell A1 causing Joe Allen to move to cell A2 on both sheets. However, it is cell A1 which is still coloured red which now makes Sue Abbott appeared to be coloured instead. Basically every time a new client is added all of the formatting and colours go out of sync as the data is shifted down 1 cell.

Is there a way to keep the colour with the data rather than the cells?

Thanks in advance for any help you can offer, this has had me stuck for too long now.
I am hoping there is a very simple solution.

It sounds as though the users of the slave sheet are filling the cells manually with a fill color. Is that accurate? Is the original record, "Joe Allen" remaining colored?

What are the rules or criteria for having a cell filled in with a specific color? Are there any unique characters or values that could be used or tracked to incorporate a conditional formatting rule?

Is the slave sheet refreshed routinely or copied over by the master copy? Would rules added to the slave sheet be overwritten when the master sheet is updated?

Hi BoardGuy, that is correct. The users are just manually adding fill colour to the clients names whenever they check the client's paperwork. The colours do remain on the slave sheet, they dont transfer back to the master though.

There are no rules for the colouring as it is done manually. I did think about trying to use conditional formatting but there is no way of doing this because the paperwork checks are done randomly and therefore no set way or time in which the names are coloured. The only details on the spreadsheet are the clients name and workplace so no unique characters that I can think to use for cond. formatting.

The slave sheet is updated regularly. This is done by the user being prompted to update each time the spread sheet opens.

Thanks for the reply