How do I pull in a value using 2 criteria?
I want to use Project Name and Resource name as a criteria to pull hours worked into a different sheet. Please help.
Best Answer
-
Hi @Jess A
Glad it helped, you could use IFERROR to remove the #invalidvalue?
So
=IFERROR(Your formula, "")
will replace the #invalidvalue with a blank. If you want a 0 then add 0 where the "" are or indeed any other text inbetween "".
Answers
-
Hi @Jess A,
Assuming that you have two columns in the sheet you are pulling the information into with Project Name and Resource Name then you could use Index Collect
Assuming that [Project] and [Resource] are the columns that you are matching the info in the sheet you are pulling the Hourse Worked into, then:
=INDEX(COLLECT({Hours Worked},{Project Name},[Project]@row, {Resource Name}, [Resource]@row),0)
When you are creating the above formula don't manually insert the {} values you need to create cross sheet references within the formula
Hope this helps
-
hi @Jess A, what’s the format of the destination sheet(s)? Would be helpful to describe your workflow case a bit more. You might benefit from strategic use of Reports depending on your business goals here. Would love to help once I understand your need a bit more.
Best,
Will
-
Thank you very much! The formula worked when I changed the 0 to a 1.
Follow up questions, some of the projects are not in my reference sheet, so I am getting an #invalidvalue. Is there a way to either return a 0 or remove these? I applied the formula to my entire column.
-
Hi @Jess A
Glad it helped, you could use IFERROR to remove the #invalidvalue?
So
=IFERROR(Your formula, "")
will replace the #invalidvalue with a blank. If you want a 0 then add 0 where the "" are or indeed any other text inbetween "".
-
@Gillian C Thank you so much! You are a life saver!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!