How can I pull data from another sheet using Index(Collect?

Andrew Ryback

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!

Lucas Rayala

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.

Lucas Rayala

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.

Andrew Ryback

Amazing! Thanks so much, @Lucas Rayala - this is exactly what I was looking for. Appreciate the help.