# Populate cells based on conditions across range of cells

1. ## 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

2. ## Re: Populate cells based on conditions across range of cells

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

3. ## Re: Populate cells based on conditions across range of cells

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.

4. ## Re: Populate cells based on conditions across range of cells

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.

5. ## Re: Populate cells based on conditions across range of cells

Hi

Have a look at the attached file.

rylo

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1