Names in column brought over to Column titled after name?

santiago.rendon

Hello! Sorry for the confusing title.

I have a sheet that users will manually input %'s into. The sheet has 3 columns, Task #, Names, and %.

I basically want to send over the %'s into another sheet that tracks everybody's % based on the Task # and who's name is in the "Names" Column.

Sheet 1

Sheet 2

So I want to create a formula that will send over the 10% (in the first sheet) under "Noah's %" Column in the second sheet.

My first thought was doing an INDEX/MATCH formula but I either get #NO MATCH or #CIRCULAR REFERENCE. I'm not sure how to get smartsheets to match the names in "Name" column to the corresponding columns of the persons names.

Melissa Yamada

Hello @santiago.rendon

try this:

=INDEX(COLLECT({%},{Task #},[Task #]@row,{Name},@cell="Noah"),1)

Note: You need to change the hard coded name ("Noah") for every column.

santiago.rendon

@Melissa Yamada I am getting #INVALID VALUE

=INDEX(COLLECT({DRR %}, {DRR % Task #}, [Task #]@row, {DRR Name}, @cell = "Noah"), 1)

is my formula

Melissa Yamada

Hello @santiago.rendon

I think it has something to do with your Task # since those are numerical values.

Try adding an IFERROR function like this:

=INDEX(COLLECT({DRR %}, {DRR % Task #}, IFERROR(@cell = [Task #]@row, ""), {DRR Name}, @cell = "Noah"), 1)