Linking cells across different files + backgrounds etc.

[Warlocks]

Hi,

I am trying to split an Excel file into 4 smaller chunks so that different people can update their own bit rather that a single person having to make all the changes.
Therefore, I will still need a Summary Excel file that will collate the 4 different sheets.
I can link the Cell data so that the summary sheet is updated automatically but my problem is that we use a colour scheme (using Cell Background) to indicate certain items and I can't seem to copy these changes into the Summary sheet.

For example, the sheet currently consists of:

----------------------------
PART A
----------------------------
PART B
----------------------------
PART C
----------------------------
PART D
----------------------------

To ensure data isn't lost/overwritten, we only give access to a single person for updates. This is no longer feasible and so we need to split the work so that each department updates their own part.
However, all the data is useful to each other and therefore we still need a summary sheet to collate all the information back to how it looked before.

I can easily link the data back together without issue.
However, as part of the information, we also use background colours to easily indicate certain items.
My problem is that I can't link the background colour information across in the same way as I can link the data.

Any ideas?

Cheers,

John.

 

[SkipVought]

Hi,

What is the criteria for the color scheme? How are the cells colored initially?

"I can't seem to copy these changes into the Summary sheet."

BTW, you are not copying ANYTHING. You are returning VALUES in a formula using functions, it seems. Formatting is not part of the returned VALUES.

Also, there are inherent problems in splitting cohesive data into chunks and then trying to reassemble into a cohesive table, and your life has been touched accordingly. You might want to take a look at using the Shared workbook feature, but it also has drawbacks. What you have is a DATABASE task that you're trying to implement in a SPREADSHEET tool.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Warlocks]

Tell me about it... (your observation on the DATABASE task in a SPREADSHEET tool)...
We are aware og this and the company is in the process of procuring a tool...
The actual spreadsheet is a Resource Planner and the colours would indicate when someone is on leave etc.
However, having the full sheet visible is extremely useful for Project Managers to know when someone could carry out their work etc.
Hence why the colours are not calculated on a formula basis...
I know there are issues but this is a "quick fix" to solve a problem that will go away when a new (suitable) system is found...

Cheers,

John.

 

[SkipVought]

Then COPY the data cells on each sheet and PASTE into the summary sheet at the appropriate row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}