Forumla Help: IF Statement with Vlookup Across Sheets

Amelia Rickard

I have created two resourcing documents (pictures below for a visual)

• Resourcing Sheet - This is the sheet where we resource each project by person
• Resourcing Rollup- The sheet is rollup to show how many hours the are allocated each week by person and gives a rollup of hours

What I am trying to do:

• Look up (example name) Bob Smith on the Resourcing Rollup > Find him on the Resourcing Sheet
• And if anywhere on the Resourcing sheet next to his name it says "Yes" under the "Shared Column" I need to write a formula on the Rollup Sheet where it checks the box on the Rollup Sheet
• Important note: Bob Smith may have 5 projects he is resourced for, but only (2) may say "Yes" and the other cells will be blank.
  • So if ANYWHERE it says yes next to his name the formula should read where it checks the box
  • However, If all cells in the Shared Column in the Resourcing Sheet are blank, the box on the Rollup Sheet should not be checked

I wrote an If statement with a Vlookup, but it only works if all the cells in the "Shared" column say "Yes" > If some cells say "Yes" and some are blank it will not check the box in the rollup sheet.

Formula here:

• =IF(VLOOKUP(Name@row, {Shared Resource}, 3, false) = "Yes", 1, 0)

Amelia Rickard

Actually I answered my own question :) It ended up being

• =IF(VLOOKUP(Name@row, {Shared Resource}, 3,false)> 1, 1, 0)

But I used the one below instead to account for anyone who isn't on the sheet so errors don't come up

• =IFERROR(IF(VLOOKUP([Name]@row,{Shared Resource}, 3, false) > 1, 1, 0), 0)

Amelia Rickard

@Paul Newcome or @J. Craig Williams any ideas?

Amelia Rickard

Actually I answered my own question :) It ended up being

• =IF(VLOOKUP(Name@row, {Shared Resource}, 3,false)> 1, 1, 0)

But I used the one below instead to account for anyone who isn't on the sheet so errors don't come up

• =IFERROR(IF(VLOOKUP([Name]@row,{Shared Resource}, 3, false) > 1, 1, 0), 0)

Marcela

Hey @Amelia Rickard

Thank you for posting your solution. I'm glad you got this resolved!

J. Craig Williams

@Amelia Rickard

Good job on keeping at it.

Craig