Formula @row = a score

I have 2 sheets, 1 sheet is the inventory smartsheet the other is the scoring worksheet based on the inventory items. I am trying to create a formula that will pull in the score from the scoring worksheet to the inventory smartsheet.

For example on the inventory worksheet-Column Name is "Access control Type". When someone chooses "unknown" in this column the score "25" (which comes from the scoring sheet) will populate in the "Access control score" on the Inventory smartsheet .

There are multiple access control types with different scoring associated.

How do I create the formula to populate the scoring based on the Access control type chosen?

Find more posts tagged with

Accepted answers

Jason Duryea

Try using INDEX and MATCH together. The formula below should be close to what you need.

=INDEX({Range 1}, MATCH([Access control Type]@row, {Range 2},0),2)

Set an external ref {Range 1} to include the range of both columns in the Scoring Worksheet.

Set an external ref {Range 2} to include only the [Access Control Type] column in the Scoring Worksheet.

Scoring Worksheet

[Access Control Type] [Score]

Unknown 25

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

All comments

Jason Duryea

Try using INDEX and MATCH together. The formula below should be close to what you need.

=INDEX({Range 1}, MATCH([Access control Type]@row, {Range 2},0),2)

Set an external ref {Range 1} to include the range of both columns in the Scoring Worksheet.

Set an external ref {Range 2} to include only the [Access Control Type] column in the Scoring Worksheet.

Scoring Worksheet

[Access Control Type] [Score]

Unknown 25

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Alisia Gill

Thank you, that formula worked.

Alisia Gill

Will this same formula work if when the type is a yes or no answer and when yes is chosen it will populate the score and if no is chosen will populate a (0)zero?

Alisia Gill

It is the same concept as the original request except for on the Inventory worksheet the user answers "Yes" or "No". When a user chooses "Yes" on the Inventory Worksheet the number "20" will auto populate in the scoring column on inventory worksheet. The number "20" is the scoring from the from the Scoring worksheet.

If the user chooses "No" on the Inventory Worksheet, the number "0" (zero) will auto populate in the scoring column on inventory worksheet. There is no scoring on the Scoring worksheet for the "no" event.

Jason Duryea

I’m not quite sure I follow your scenario with [Access Control Type] being yes or no, it should be able to match against any column type / value combination I believe.

”Yes” or “No” if a text or drop down value

“True” or “False” if it’s a checkbox column type

Alisia Gill

The column name on the Inventory worksheet is Adobe with a dropdown of yes or no. If yes is chosen on the Inventory worksheet it will bring back a value (from the scoring worksheet) and if no is chosen it will bring back a 0(zero)

Thank you

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.