Hi,
I'm attempting to put together a spreadsheet that measures the difference between an actual assessed score for a person and their "ideal" score. The assessed scores are entered into the sheet manually and then then the user can double click on a score between 1 and 7 to denote their "ideal" score (for a range of components). Then the gap between the assessed and ideal will be calculated for each component. (Hope that makes sense) :-)
So, what I have done so far is insert some Visual Basic, so that one can double click on a score between 1 and 7 for all the components and then that score will be highlighted in blue. (Each score is in an individual cell). This all seems to work well. What I needed to do then was create a function that would look at each cell of the 7 and see whether it had been highlighted or not.
I then created 7 new functions called colour7, colour6, colour 5 etc This is identify for each of the 7 scores, whether they have been selected or not. The example below is colour7. colour6 would have "-6" at the end as it would denote 6 columns to the left etc.)
=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-7))
[ Details of the GET.CELL macro function can be found here http://www.mrexcel.com/forum/excel-q...arguments.html ]
In the 'result cell I have the formula:
=IF(Colour7=37,C5,"False")
So, if the cell is blue, which is code 37), the result would return the contents of the cell, otherwise it would just return "FALSE"
This is all good and well, but my problem is that the result does not update automatically. So if I double click on the cell to select a score, or unselect as the case may be, the result does not update automatically. If I click on Calculate now the result cell updates. I have checked the calculation options and they are all set to automatic, so I am at a bit of a loss. Can anyone help?
Thanks,
Mark
Bookmarks