Is there a way to pull a list of names from a sheet based on another name?

nkilburn

I have a sheet that has employee names, supervisors and some other data. I am trying to have Smartsheet automatically pull the names from the sheet and input them into a metric sheet. I can Make INDEX MATCH work with a some changes but only if I know the exact number of rows on the Metric sheet.

Samuel Mueller

@nkilburn using a couple columns you could achieve this.

On your destination sheet you need an auto number column, and then a helper column with a match formula. Autorow called ID, helper column called RowNum

column formula in RowNum "=match(id@row, RowNum:RowNum, 0)

Then in your column with the index match, your formula should be,

=IFERROR(Index(Distinct({Employee Name}), RowNum@row), "")

then for each of the other columns use an index match on employee name..

The issue though is that you need a large number of rows to be populated, because smartsheet does not automatically create rows. So I would maybe go into excel and copy something to 500 rows, copy that and paste it into your sheet to populate 500 rows, and then just delete the data out of smartsheet.

Let me know if that works or if you have other questions.

nkilburn

This is my formula currently,

=INDEX({Employee Name}, MATCH("Sup. Name", {Sup name}, 1) + [Row ID]@row - 1)

I understand that the change in the value in the end of the INDEX formula is changing where the formula is actually looking. Is there a way for it to stop if the sup. name is not correct?

Samuel Mueller

@nkilburn try wrapping it in an iferror

=IFERROR(INDEX({Employee Name}, MATCH("Sup. Name", {Sup name}, 0) + [Row ID]@row - 1)), "")

But I would use my distinct formula for the employee name, then do an index match for the other columns based on employee name. I misinterpreted that in my last comment

formula in employee name: =IFERROR(Index(Distinct({Employee Name}), RowNum@row), "")

formula in supervisor name: =IFERROR(INDEX({Sup name}, MATCH([Employee Name]@row, {Employee Name}, 0)), "")

You should pretty much always use a 0 in a match formula too..

The row for the index always starts at 1 as well, not zero.

nkilburn

@Samuel Mueller I guess im not sure how to set up the RowNum Column, I am getting a circular reference.

Samuel Mueller

@nkilburn you are right, I apologize.. The formula should be..

column formula in RowNum "=match(ID@row, ID:ID, 0)

nkilburn

@Samuel Mueller This is the formula I ended with. It works well with my process thank you for your help

=IFERROR(INDEX(COLLECT({Employee Name}, {Supervisor Name}, "Supervisor Name"), [Row ID]@row), "")

Thanks again