Excel 2007 : Linking cells to other excel sheets

[crankbox-music]

Greetings Excel forum readers,

We are human resources specialists and we are about to create several excel sheets for easy data entry.
Currently, we are dealing with up to 200 individuals, who work for us (janitor, plumber, etc..) and we already have three sheets for getting a decent overview for our actual manpower. The basic data entry is this: column 1 is for the job title (we use numbers - 11 is for janitor, for instance), 2 is for names, 3 is for the stint (date when the job will be done), 4 is for the days left with conditional formatting (using happy colors) - projections for 30/60/90/120 and 180 days.

This is it for the basic sheet.

The following sheet is named roll up. The roll up contains all the summarized data from the basic sheet.
Column 1 shows the job title, let us say the janitors again. I need a formula that counts the janitors from the basic sheet - counting "11's" and that number needs to be shown on the roll up.

Column 2 (named assigned) should display that figure on the roll up. Moreover, we would like to keep on track with our losses and gains (done jobs - new jobs assigned) within a 6-month-timeframe. We have separately created losses and gains sheets with the resembance of the basic sheet.

Columns 3-7 show the 30/60/90/120 and 180 days-loss-projections. Columns 8-12 show gains projections.
I would like to create a formula that counts the conditional formatting from the basic sheet (column 4) and links it with the roll up.


The vision is the following:
Basic sheet, gains, and losses will provide the information to the roll up.

Maybe someone of you got a rough idea on how to link them up.
Meanwhile, I try to get some images of this bold plan.

Thanks in advance.

Cheers,
Chris

[EHarvill]

Hi Chris,
Can you provide examples of the spreadsheets you want to have linked?
Thanks!
Evelyn

[crankbox-music]

Sure, Evelyn

I do hope this helps. sample.jpg

This print screen displays the nitty gritty. I selected a compact way to show it. They are divided into 4 sheets, but I pasted them into one for a better understanding.

Thanks,
Chris