Forumla Help: IF Statement with Vlookup Across Sheets

Amelia Rickard
Amelia Rickard ✭
edited 10/17/24 in Smartsheet Basics

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)
Resource.png
Rollup.png

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Amelia Rickard
    Amelia Rickard ✭
    Answer ✓

    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)

    · Share on FacebookShare on Twitter

Answers

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video