help - with formula/cell linking across multiple sheets automatically, without having to manually ad

Luke James

Summary:

 

I have loads of sheets in a "project" folder, 1 for each project, which have identical layout (column names etc).

Each sheets has a region field, which is either UK, China, Japan, HK or Multi.

 

I then have reports which take the relevant information I need from all sheets in the "report" folder, which is then displayed on a Overall Project Dashboard.

 

What I need:

I have 4 boxes on on my dashboard which I want to auto populate, but im not quite sure how to achieve this. (as per screenshot - currently just rich text boxes to show how i want it to look)

Id like the boxes to display the following info, in a similar style as below: (XX = the count for each)

 

*************************************

*              UK Summary          *

*                                              *

*     XX Projects in flight        *

*     XX Projects Not Started *

*     XX Projects Complete    *

*                                              *

*************************************

All info will come from each individual Project sheet (im not sure i can achieve this with a report)

Projects in flight = Status "In Progress"

Projects not started = Status "Not Started"

Projects completed = Status "Complete"

 

So bascially Im after a count of the above 3 categories, but taking it from lots of individual sheets within the project folder, for each region.

Is there a way of achieveing this without having to link each of the cells to a central sheet each time theres a new project sheet added?

 

Hopefully this makes some kind of sense, tricky to explain

Christine Oakes

Luke

 

You can achieve this by adding two columns to each project and use a Countifs formula. Link just those cells into a central sheet to get your totals - they will update as new projects get added.

 

Once you link those into a central sheet you can get your totals for your Sight.

 

Christine