Show warning sign if a value is exceeded

[santa_man3]

Hi,

I have a spreadsheet that contains a list of projects, and resources assigned to that project per month.

I need to flag, based on the number of working days in that month, when I enter a value against a resource if I have exceed that number of working days (i.e. they are now over allocated). There are multiple rows for the same resource as they could be working on different projects.

How do I do this? Can I use data validation and a formula or do I need a macro?

Thanks

[mcmahobt]

Can you upload a sample workbook that shows how you want your expected results?

[santa_man3]

Hi,

Attached is an example of the columns I need to work with.

[santa_man3]

So next to the date it shows there are 22 days in March.

Resources are on multiple rows, so when I enter a value in any of the rows against each resource, I want to be warned that in total, for that resource, I have exceeded 22 days.

I've put a conditional format in to flag each cell red if the value in that cell exceeds 22 days, but I need the total across all rows to flag as exceeded.

[mcmahobt]

See attached for a possible solution. This is assuming you keep the same resource format in row 1.

[santa_man3]

Sorry, where am I looking? I can't see anything...

[mcmahobt]

I have attached a workbook that incorporates a conditional formatting solution. Can you not see the workbook?

[santa_man3]

Oh you used conditional formatting, ok.

I've tested it but it doesn't work - I can enter the value 10 in cell B2 and it doesn't flag that "Fuller, James" is then over allocated based on the 22 working days in March.

[mcmahobt]

How is Fuller over allocated if 10<22?

[santa_man3]

Because they exist on rows 2, 3 and 9. And could be added into new rows too when additional projects get added to the spreadsheet (not included in this example).

I have already put the conditional formatting in place to account for the single value in each cell, but its the count of value per month, per resource I need to have flagged.

Does that make sense?

Thanks

[newdoverman]

Maybe this will work for you. I changed the headers of the columns so that the cells could be filled across and give the months and inserted a row under that which calculates the working days for each month. You will need to fill in the holiday list as I have no idea of what your holidays are.

[mcmahobt]

I have already put the conditional formatting in place to account for the single value in each cell, but its the count of value per month, per resource I need to have flagged.

Still trying to understand what you mean by this...does this mean that cell B4 should be flagged for March, since 12+10>=22 which exceeds the resources for the month?

[santa_man3]

Perhaps I'm not explaining myself clearly enough...

I know how many working days there are in the month (taking into account holidays) so I don't need to calculate that.

In column A, I have a list of resources. The same resource can appear several times in that list - take "Fuller, James" for example in row 2, 3 and 9.

We know that in March there is a max of 22 days which can be worked per resource.

In cell B3, we have 12 days allocated to Fuller already. Which leaves him with 10 days under allocated. If we put the value 15 in cell B2, I want to be warned that Fuller is over allocated as this would total the number of days allocated to him to (B2+B3 or 15 + 12 = 27)

[newdoverman]

I have made a change to the Conditional formatting rule. This totals each person for the month and if the total exceeds the monthly total of working days, all the cells for the month for that person is coloured RED.

[santa_man3]

Hi,

Thank you - we're nearly there.

However, it is only turning all cells for that resource red if the value in ONE cell exceeds the monthly limit.

I'm still able to put 22 days for Fuller, James in March several times without it flagging red. E.g. put 22 in cells B3 and B4 it doesnt flag red because individually the values don't exceed the 22 limit, but obviously this would mean Fuller is 100% over allocated.

[newdoverman]

Why would you only want 1 cell to turn red when all cells contribute to the total?

Change the formula for Conditional Formatting to the following and this will highlight all cells for an individual if the total for that individual is greater than the number of workdays for the month:

Please Login or Register  to view this content.