Mimic name manager (excel functionality) and logic in Forms

I have a sheet with a column that contains a column formula to index, collect a response from a "metric" sheet. I would like to include this column as a hidden field in a form so that I can apply conditional logic to then display the corresponding column and dropdown list for the user to select. This is a workaround solution I came up with to in a way mimi name manager excel functionality.

So in other words if the hidden field in the form is ABC, then dropdown column 1 will display with specific dropdown values that align to content in the ABC criteria field (that is currently driven by an index, collect formula).

The form will not allow me to add this hidden column if the formula is set to column formula, but then the scenario doesn't work if I don't have the formula to drive the response.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Melissa Torrez

Smarsheet forms are unable to display or use formulas: formulas will only calculate once the form has been submitted and the content appears in a row in the sheet.

Is there another way to potentially drive your conditional logic? If you could post a screen capture of your form and another one of your sheet in Grid view, explaining what it is you're looking to hide/show, we may be able to help think of an alternative way to accomplish your goal.

Cheers,

Genevieve

Melissa Torrez

Hi @Genevieve P

Below is a screen shot of the sheet. Ideally, I wanted to add the Stream column/ field as a hidden field to the form to then drive conditional logic such that if stream is PTP, the PTP Assigned To field would display in the form allowing the user to select form a specific list of names associated with the PTP workstream. The reason I want to hide the stream field in the form is because I am using an index/collect formula to determine the stream based on 2 other selections in the field (function and system). I only want to user to see the assigned to dropdown list that corresponds to the stream as determined by selected function and system. See below, in the form snapshot the user will select the finance function and system, stream would then auto-populate in "the background" as the hidden field and then drive the logic for which assigned to list should display.

Does that make sense?

Genevieve P.

Hi @Melissa Torrez

Thank you for clarifying further!

So there isn't a way to do this based on your current set-up. A form won't be able to read the formula in the sheet, and the criteria that determine the Stream are two overall fields.

Is there a way to combine those two criteria together, the Function and System? Then you can base your logic off of the selections made from that one column.

For example, you could either set it up as a single select column with every possible combination listed, or perhaps a multi select column with each Function listed and each System listed, identifying the form user needs to choose one of each. Then based on the combination of selections you could display certain fields, like so:

Screen Shot 2021-05-04 at 2.15.13 PM.png

Would you be able to identify what needs to be chosen for each stream? What does the reference sheet look like that identifies the stream?

Let me know if this would work for you or not!

Cheers,

Genevieve

Melissa Torrez

@Genevieve P I think this could be a viable solution, however is there a limit to how many logic conditions you can add? I would have to create a 144...

Genevieve P.

Hi @Melissa Torrez

Yes, there is a limit... the limit varies by the size of the rules (how many fields are selected) and the number of rules on a single source field. 144 will definitely be too many. I would normally break it out so there's less than 10 rules on one field, so I don't think this is a viable solution.

Does this mean you have 144 columns to show? Or do you mean that you have 144 possible selections?

Another idea would be to break up your process so that after they submit the initial form, once the formula determines the Stream, it sends out an Update Request showing only the next required fields (based on that Stream) for them to fill out as a follow-up.

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.