Populate cells based on conditions across range of cells

Populate a cell based on range and conditions.

--------------------------------------------------------------------------------

I am attaching the excel sheet with my requirements. Unfortunately thats how it is.
I have a created a series of IF statements on cell B4, B5, B6, B7 and B8 on the Main Utilization Sheet with the condition.
This condition says for a particular week of the month, a person could work on multiple projects. Based on the number of hours he spends each week filled against the individual sheets for each month, the Main sheet will sum up and provide remaining hrs in a month, so that he could be utilized in other projects if required.
Can you please help in how to write a macro for this so that I can avoid the multiple IF statements in each cell

Hi

You could do this with formulas.

In Main Utilization Sheet:
1) B1: =MATCH(B3,Jan!$A:$A,0)
2) B4: =SUM(OFFSET(Jan!$A$1,'Main Utilization Sheet'!B$1+2,MATCH($A4,INDIRECT("Jan!" & B$1+2 & ":" & B$1+2),0)-1,11,7))
3) Copy from B4 to cover the range B4:F17.

You know the relevant sheets, so you can adjust the formulas to replace the relevant sheet names.

HTH

rylo

Hi,
Thanks for the reply. I tried the B4 part, but this part MATCH($A4,INDIRECT("Jan!"&B$1+2&":"&B$1+2),0) returns #NA. Maybe I am missing some part here.

I apologize. I did give it a try by referencing the correct cell numbers. It is working on one of the cell. I will get it a try in the rest of the sheets and let you know in case it does not work.

Hi

Have a look at the attached file.

rylo