How to use index collect?

Brandon Morales

Hi, I'm trying to write a formula that will look at a particular column (in this case the "extrusion" column) and return the first entry that has a check in the "cycle count is due column". My current formula is below and comes up unparseable.

=INDEX(COLLECT({Metal- Shop Floor Inventory Range 1}, {Metal- Shop Floor Inventory Range 2}, 1)))

Kelly Moore

Hey @Brandon Morales

To verify, Range 1 = Extrusion column and your Range 2 = Cycle Count column, correct? This would be the correct syntax for the formula.

You completed the COLLECT of the formula above, however you did not complete the INDEX portion of the formula. You're missing the [row index], and this will also allow you to grab the first instance that the COLLECT finds. You also have an extra parenthesis.

=INDEX(COLLECT({Metal- Shop Floor Inventory Range 1}, {Metal- Shop Floor Inventory Range 2}, 1),1)

Does this work for you?

Kelly

PS - As a good practice you can name your cross sheet references before inserting them into your sheet. Simply replace the generic Range # with your column name and you have both the sheet and column identified for future reference.

Kelly Moore

Hey @Brandon Morales

To verify, Range 1 = Extrusion column and your Range 2 = Cycle Count column, correct? This would be the correct syntax for the formula.

You completed the COLLECT of the formula above, however you did not complete the INDEX portion of the formula. You're missing the [row index], and this will also allow you to grab the first instance that the COLLECT finds. You also have an extra parenthesis.

=INDEX(COLLECT({Metal- Shop Floor Inventory Range 1}, {Metal- Shop Floor Inventory Range 2}, 1),1)

Does this work for you?

Kelly

PS - As a good practice you can name your cross sheet references before inserting them into your sheet. Simply replace the generic Range # with your column name and you have both the sheet and column identified for future reference.

Brandon Morales

Eureka!! thanks so much for the help. Also, thanks for best practice tip with naming my references- its a habit I need to work on. Thank you again.