Pull info from another sheet based on 2 criteria
I'm trying to pull in hours to what I'll call Sheet 1 from Sheet 2. I have flexibility in the way I set up Sheet 2, but I need Sheet 2 to be able to have various employees use the same project number. This would be one option for Sheet 2:
In sheet 1, I will have the project number listed in a column, but will only have the project number listed once in the sheet. I want to put the hours for each project under the correct employee column, such as below.
I'm trying to use the INDEX COLLECT formula and I was hoping to set two criteria. In the above image I only have one criteria set, the project number. I'm not concerned about the invalid value, I know how to fix that. But, when I try to put in the second criteria as who it is assigned to, I get an incorrect argument message. For the image above I was just using the formula:
=INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row), 1)
This doesn't allow me to use a similar formula for the Johnny and Dolly columns.
I tried using =INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row, {Sheet 2 Assigned To}, "Willie", 1)) but that gives me #INCORRECT ARGUMENT.
I first tried VLOOKUP, but that wouldn't let me reference the project number more than once from what I could tell.
Any help is greatly appreciated!
Best Answer
-
@CF_Project_Coord206, you have an extra close parenthesis after your [Project Number]@row. Remove that and you should be good.
=INDEX(COLLECT({Formula Test Task List 2 Hours}, {Formula Test Task List 2 Project Number}, [Project Number]@row, {Formula Test Task List 2 Assigned To}, "Willie"), 1)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
Your parentheses are in the wrong place. You need to close the collect, then a comma 1 then final close parentheses. So it should look like this:
=INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row, {Sheet 2 Assigned To}, "Willie"), 1)
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Thanks for the assistance. When I fixed the formula to this:
=INDEX(COLLECT({Formula Test Task List 2 Hours}, {Formula Test Task List 2 Project Number}, [Project Number]@row),{Formula Test Task List 2 Assigned To}, "Willie"), 1)
I am now getting an #UNPARSEABLE message. I copied the formula I have exactly so that I could make sure there were no mistakes. My sheet name "Formula Test Task List 2" in the above formula is the same as what I call Sheet 2 above. The [Project Number]@row is referencing the cell in Sheet 1 that has the project number.
Thanks again for your help!
-
@CF_Project_Coord206, you have an extra close parenthesis after your [Project Number]@row. Remove that and you should be good.
=INDEX(COLLECT({Formula Test Task List 2 Hours}, {Formula Test Task List 2 Project Number}, [Project Number]@row, {Formula Test Task List 2 Assigned To}, "Willie"), 1)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
This worked! Thank you so much @Jason Tarpinian and Michelle Choate!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 412 Global Discussions
- 221 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 461 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!