Display cell contents in another cell based on dropdown selection in a column.

Hello all,

I have recently started using smartsheet for H&S audits.

I have compiled an audit report as per the image. The report counts the number of "Yeses", "Improves", "Nos" and "Not Applicables/Not Checked". The sum is displayed in the table at the bottom right corner. Scoring is also calculated based on a formula. Everything works fine, however, I am struggling with one function.

Ideally we would also like to have

all "Open" question numbers (28.1, 27.4, 27.4, 27.3....26.2...1.1) or ideally the whole question displayed in the cell at the bottom left corner (where currently it states "incorrect argument set")
all "Closed" question numbers or ideally the whole question displayed in the cell underneath it.

Any help would be appreciated.

Thank you.

Find more posts tagged with

Accepted answers

All comments

Ayelet Weiner

You should be able to accomplish what you need with JOIN and COLLECT. Here is a great post that has a good breakdown.

https://community.smartsheet.com/discussion/65338/join-collect-formula

For the formula to the right of OPEN, it would look something like this:

For Closed:

What these functions allow us to do is identify the range of values that we want to Join and identify the range of values we want to collect only IF the status meets your criteria. I highly recommend looking at the documentation for these functions.

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

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

Hope that helps!

Kristian Gradev

Hello Ayelet,

Thank you very much for the reply - really appreciate it.

Unfortunately I am getting "Unparseable" when I input the formula.

Thank you very much in advance.

Paul Newcome

I personally would suggest creating reports pulling from the source sheet and filtered appropriately then providing a hyperlink to the reports.

If you still wanted to output the list of open numbers and are getting the error with the join/collect, are you able to provide a screenshot of the formula open in the sheet (as if you are about to edit it)?

Kristian Gradev

Hi Paul,

Thank you very much. I would really like of the numbers are displayed in these cells.

Please see below the two snapshots. Thank you very much in advance.

Paul Newcome

Looks like you are just missing a comma there after the second to last closing parenthesis.

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.