hobbiton73
11-17-2012, 08:17 AM
Hi I wonder whether someone may be able to help me please.
I'm using the attached file to records staff resource forecasts.
You will see on the spreadsheet that the user enters their name in column A and then plots their time resource in columns B to M under each month within the financial year.
Row 3 in columns B to M contain the following formula =SUBTOTAL(9,B7:B10), so when the user filters on their name it returns the total amount of days they have entered for each month.
Row 4 is the total amount of working days for each monthUnfortunately some users are not particularly well versed in the use of Excel which creates errors in the data they enter.
So what I'd like to be able to do is, if the user applies the autofilter on their name, and the total amount of days they have entered in rows 7 to 10 is greater than the possible number of working days I would like to apply conditional format to the subtotal cell, highlighting the font in red. So inessence I want to apply Conditional Formatting only when the spreadsheet has been filtered.
If we use Chris as an example.
Filtering on Chris, will show that he has allocated 25 days in April to his areas of work, when in fact there is only a possible total of 21 working days. I would like the value in cell B2 to be in a red font.
I've spent days trying to find a solution to this, but I haven't been able to find a solution.
I just wondered whether someone could possibly take a look at this please and offer some guidance on how I may be able to achieve this.
Many thanks and kind regards
I'm using the attached file to records staff resource forecasts.
You will see on the spreadsheet that the user enters their name in column A and then plots their time resource in columns B to M under each month within the financial year.
Row 3 in columns B to M contain the following formula =SUBTOTAL(9,B7:B10), so when the user filters on their name it returns the total amount of days they have entered for each month.
Row 4 is the total amount of working days for each monthUnfortunately some users are not particularly well versed in the use of Excel which creates errors in the data they enter.
So what I'd like to be able to do is, if the user applies the autofilter on their name, and the total amount of days they have entered in rows 7 to 10 is greater than the possible number of working days I would like to apply conditional format to the subtotal cell, highlighting the font in red. So inessence I want to apply Conditional Formatting only when the spreadsheet has been filtered.
If we use Chris as an example.
Filtering on Chris, will show that he has allocated 25 days in April to his areas of work, when in fact there is only a possible total of 21 working days. I would like the value in cell B2 to be in a red font.
I've spent days trying to find a solution to this, but I haven't been able to find a solution.
I just wondered whether someone could possibly take a look at this please and offer some guidance on how I may be able to achieve this.
Many thanks and kind regards