Forumla Help: IF Statement with Vlookup Across Sheets
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)
Best 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)
Answers
-
@Paul Newcome or @J. Craig Williams any ideas?
-
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)
-
Hey @Amelia Rickard
Thank you for posting your solution. I'm glad you got this resolved!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!