how can I add on the roll up table my contact list of mangers so that the formula work

Gaia Amaia

i have tried adding the email - the name but it doesn't work !

this is the formula I have used and referenced the assigned to and status column

Georgie

Hi @Gaia Amaia,

From your screenshots, it looks like these sheets were built using the “Construction and Facilities Management” template set. 

Having reviewed that template set and your screenshots, I can see that the main issue is that you’ll need to add the names of your managers in your “MOD2 - Main Roll-Up” sheet, in the Primary column under “Resource Metrics”. You’ll only need the first name and last name, but these must match the name as it appears in the contact cell in the project sheets. So, where the contact displays as “Gaia Amaia” in the project sheets, you can type in “Gaia Amaia” into the cell in Primary7 - see an example below:

 

The COUNTIFS formulas in the Totals column under “Resource Metrics” reference the cells in the Primary column, so they need the exact names present here so that they “know” what to look for in the project sheet ranges. 

Currently, the formulas use a cell reference (eg “Primary7”) to reference the cell in the Primary column in the same row, but you can change this to @row, since in this case we always want to look for the value in the same row. So you can change the formula to:

• =COUNTIFS({PPM Project 1 Range 1}, Primary@row, {PPM Project 1 Range 2}, <>"Complete") + COUNTIFS({PPM Project 2 Range 1}, Primary@row, {PPM Project 2 Range 2}, <>"Complete")

You’ll then be able to drag this down to all other cells under the “Resource Metrics” heading. You can see this formula in my test sheet below:

Take a look here for more information on @row: Create efficient formulas with @cell and @row.

The final thing I spotted is that in your “MOD2 - Rack B4” sheet, the only row I can currently see a contact on is row 3, which has a status of “Complete”. The current formulas in the Roll Up sheet are looking for rows that match the name in the same row and have a status that is not equal to Complete. To ensure that row 3 would be counted, it would need to have a different status. Alternatively, you can edit the formula to look for all tasks assigned to each user, whichever status they have. If you’d like to do that, your formulas in the “Resource Metrics” section of the roll-up sheet would look like this:

• =COUNTIF({PPM Project 1 Range 1}, Primary@row) + COUNTIF({PPM Project 2 Range 1}, Primary@row)

Hope that helps!

Georgie

Georgie

Hi @Gaia Amaia,

From your screenshots, it looks like these sheets were built using the “Construction and Facilities Management” template set. 

Having reviewed that template set and your screenshots, I can see that the main issue is that you’ll need to add the names of your managers in your “MOD2 - Main Roll-Up” sheet, in the Primary column under “Resource Metrics”. You’ll only need the first name and last name, but these must match the name as it appears in the contact cell in the project sheets. So, where the contact displays as “Gaia Amaia” in the project sheets, you can type in “Gaia Amaia” into the cell in Primary7 - see an example below:

 

The COUNTIFS formulas in the Totals column under “Resource Metrics” reference the cells in the Primary column, so they need the exact names present here so that they “know” what to look for in the project sheet ranges. 

Currently, the formulas use a cell reference (eg “Primary7”) to reference the cell in the Primary column in the same row, but you can change this to @row, since in this case we always want to look for the value in the same row. So you can change the formula to:

• =COUNTIFS({PPM Project 1 Range 1}, Primary@row, {PPM Project 1 Range 2}, <>"Complete") + COUNTIFS({PPM Project 2 Range 1}, Primary@row, {PPM Project 2 Range 2}, <>"Complete")

You’ll then be able to drag this down to all other cells under the “Resource Metrics” heading. You can see this formula in my test sheet below:

Take a look here for more information on @row: Create efficient formulas with @cell and @row.

The final thing I spotted is that in your “MOD2 - Rack B4” sheet, the only row I can currently see a contact on is row 3, which has a status of “Complete”. The current formulas in the Roll Up sheet are looking for rows that match the name in the same row and have a status that is not equal to Complete. To ensure that row 3 would be counted, it would need to have a different status. Alternatively, you can edit the formula to look for all tasks assigned to each user, whichever status they have. If you’d like to do that, your formulas in the “Resource Metrics” section of the roll-up sheet would look like this:

• =COUNTIF({PPM Project 1 Range 1}, Primary@row) + COUNTIF({PPM Project 2 Range 1}, Primary@row)

Hope that helps!

Georgie