 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I've two sheets - one with details of project milestones, one with a number of projects listed.
so
PROJECT 1
- milestone 1
- milestone 2
- milestone 3
PROJECT 2
- milestone 1
- milestone 2
- milestone 3
etc
then
PROJECT 1
PROJECT 2
PROJECT 3
etc
When a milestone is completed, I'm changing the cell to show whether it's complete, not complete or nearly there. This is colour coded using the Excel 2010 'good/bad/neutral' cell shading options - so, green, red, yellow.
How can i get the project overview sheet to read these cells and format accordingly? i.e. if one of the milestones is amber, the overview shows red or whatever.
Use a formula and conditional cell formatting?
So set up a cell on the master sheet to be something like:
=IF(Sheet2!G9="PASS",1,0)
and then apply conditional formatting to the master cell, so it is Green for 3, Yellow for 2 and Red for 1 (or whatever).
To link to multiple sheets:
=IF(Sheet2!G9="PASS",1,0)+IF(Sheet3!G9="PASS",1,0) etc
If you projects are split on the first sheet between diffenret columns you could use a vlookup to look for anything that is flagged as a risk.
If you have all you milestones in the same column you could set up named ranges which relate to the project range (insert-Name-Define, choose range and name it) then add this to you vlookup i.e. =vlookup('Bad',Project1,1,false).
This assumes you're writing things in the cells next to the milestones and aren't using colour coding.
you cant test the colour of a cell, only it's contents (or its location). SO you need to bring some kind of logical value back from the milestones sheets to the Project sheets first. Then conditionally format the cell in the project sheet on that value.
Do you work in marketing? Colour coding excel sheets is a particular penchant of the fluffier areas of business but leads to no end of issues when you atually try to use the 'data'
However this
you cant test the colour of a cell,
Isn't quite true, it can be done with a bit of VBA script and a custom funtion.
But it will be far easier just to do what everyone else above sugests.
Isn't quite true, it can be done with a bit of VBA script and a custom funtion.
I knew that, but didnt want to open the door to you VBA nutjobs 🙂
Do you work in marketing? Colour coding excel sheets is a particular penchant of the fluffier areas of business but leads to no end of issues when you atually try to use the 'data'
I call that the "Secretarial Spreadsheet" effect.
I call that the "Secretarial Spreadsheet" effect.
I caught someone typing numbers into excel, then adding them together with a calculator and then typing the result into the next cell.
Unfathomable
But then to make my brain explode with the pointlessness of it all they did all again and again with different sets of numbers.
What is more unfathomable is that when I told them that I could teach them how to do it Excel faster than it would take them to do one of the calculations, let alone all of them, they got mad and told me they prefer doing it their way.
Arrrrggggggg
excised from a spreadsheet this morning. Cell should contain numerical data only:
£1 to 06/04/10 £60,000 to 06/07/10 £70,000 to 06/07/11 £80,000 to 06/07/12 £90,000 to 06/07/13 £100,000 to 06/07/14 and The higher of (1) £100,000 per annum and (ii) 80% of the OMR
And dont get me started on the abominations that end up in DATE cells 😯
Yes I'm a fluffer.
Communications. But even i could do it with values - that's EASY.