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
