How can I pull data from another sheet using Index(Collect?
Hi everyone.
I am trying to integrate an INDEX(COLLECT function into an existing sheet, but I am having issues with the formula.
I am building a report template and am trying to pull equipment assignments from another sheet. So, if I type in the project name into my report template, I want to auto-populate the 'PCR' (internal equipment assignment) that's currently assigned to that project from my production calendar sheet.
I'm not 100% sure if the INDEX(COLLECT is the right function, but I am struggling to get this to work.
Thanks in advance for the help!
Best Answer
-
Hi @Andrew Ryback, yes, you can use Index/Collect to make this happen. You need a unique ID (your project name) and it must exactly match the project name in the sheet you are pulling from. Assuming that in your report sheet you have a column named "Project" (where you will type the project name in), and in the sheet you are referencing you have two columns, one named "Project" as well (where the project name is referenced), and the other named "Equipment" (which has the internal equipment in it), then the formula would look something like this:
=INDEX(COLLECT({Equipment}, {Project}, Project@row), 1)
Don't forget the "1", it's needed for the INDEX function. You will need to create cross-sheet references for the two ranges surrounded by squiggle brackets {}. If you don't know how to do that, here's the help article:
Create cross sheet references to work with data in another sheet | Smartsheet Learning Center
If you can, I would suggest you use a pulldown menu with all your project pre-listed instead of hand-typing them in, but that might not be possible.
Answers
-
Hi @Andrew Ryback, yes, you can use Index/Collect to make this happen. You need a unique ID (your project name) and it must exactly match the project name in the sheet you are pulling from. Assuming that in your report sheet you have a column named "Project" (where you will type the project name in), and in the sheet you are referencing you have two columns, one named "Project" as well (where the project name is referenced), and the other named "Equipment" (which has the internal equipment in it), then the formula would look something like this:
=INDEX(COLLECT({Equipment}, {Project}, Project@row), 1)
Don't forget the "1", it's needed for the INDEX function. You will need to create cross-sheet references for the two ranges surrounded by squiggle brackets {}. If you don't know how to do that, here's the help article:
Create cross sheet references to work with data in another sheet | Smartsheet Learning Center
If you can, I would suggest you use a pulldown menu with all your project pre-listed instead of hand-typing them in, but that might not be possible.
-
Amazing! Thanks so much, @Lucas Rayala - this is exactly what I was looking for. Appreciate the help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!