Names in column brought over to Column titled after name?

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.

Find more posts tagged with

Formulas

Support

Accepted answers

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.