Formulaire intelligent

Bastien

Bonjour,

J'utilise un sheet avec 180 colonnes.
Dans ces colonnes je renseigne manuellement une valeur "Yes", "No" et "N/A".

Dans un autre sheet, je souhaite créer un formulaire "intelligent". Lorsque j'indique un critère particulier "ID" par exemple, présent sur les deux sheets, je souhaite que le formulaire m'affiche uniquement les colonnes avec Yes.

Exemple :

Sheet 1 :

ID : Test - 30 colonnes Yes, 25 No, 100 N/A

ID : Test 2 - 100 colonnes Yes, 2 No, 30 N/A

Sheet 2 :

J'utilise mon formulaire une première fois, je renseigne l'ID : Test, automatiquement, le formulaire m'affiche les 30 colonnes Yes.
J'utilise mon formulaire une deuxième fois, je renseigne l'ID : Test 2, automatiquement, le formulaire m'affiche les 100 colonnes Yes.

Si vous arrivez à m'aider sur ce sujet vous êtes excellents !

Bon courage 😅

jmyzk_cloudsmart_jp

Hi @Bastien

Je vous donne cette réponse en anglais afin d'assurer son exactitude technique. Veuillez utiliser un outil de traduction pour la convertir en français.

English

Your Need

You want to create a system in Smartsheet where, when a user enters an ID (e.g., "Test") in Sheet2, the system will:

• Look up the corresponding row in Sheet1
• Identify which of the 180 columns (Col1 to Col180) are marked as "Yes"
• Return the names of those 'Yes' columns to be displayed in Sheet2

My Solution (Overview)

To meet this need, I designed the following setup:

• In Sheet1, each row represents an ID with 180 columns filled with "Yes", "No", or "N/A".
• I added a column number (1–180) to each column in Sheet1 for easier reference.
• In a separate helper sheet (Sheet3), I map each column number to its corresponding column name (e.g., 1 → Col1, 2 → Col2, etc.).
• In Sheet2, I use formulas to:
  1. Match the entered ID to its row in Sheet1
  2. Extract column numbers where the value is "Yes"
  3. Use HAS() and COLLECT() with the Sheet3 mapping to return the corresponding column names

This allows Sheet2 to dynamically display the relevant column names based on the selected ID.

Sheet2

I created a second sheet (Sheet2) that enables dynamic column filtering using this logic:

https://app.smartsheet.com/b/publish?EQBCT=7314501bb80c49919075df63e79c70cf

Sheet2 (User Input + Logic):

Field	Purpose
ID	Input field to select the row from Sheet1
Row in sheet1	Finds the row number in Sheet1 for the selected ID using MATCH
Column Numbers Yes	Multi-select dropdown with number of columns that have "Yes"
Column Numbers No	Same for "No"
Column Numbers N/A	Same for "N/A"
Yes Columns	Uses COLLECT and HAS to return the actual column names from Sheet3 for matching "Yes"
(Same logic for No Columns and N/A Columns)

Sheet2 Formula

[Yes Columns] =JOIN(COLLECT({sheet3 : Column}, {sheet3 : Row}, HAS([Column Numbers Yes]@row , @cell )), CHAR(10))
[No Columns] =JOIN(COLLECT({sheet3 : Column}, {sheet3 : Row}, HAS([Column Numbers No]@row , @cell )), CHAR(10))
[N/A Columns] =JOIN(COLLECT({sheet3 : Column}, {sheet3 : Row}, HAS([Column Numbers N/A]@row , @cell )), CHAR(10))
[Row in sheet1] =IFERROR(INDEX({sheet1 : Row}, MATCH(ID@row , {sheet1 : ID}, 0)), "")
[Column Numbers Yes] =JOIN(COLLECT(INDEX({sheet1 Col1:Col180}, 1), INDEX({sheet1 Col1:Col180}, [Row in sheet1]@row ), "Yes"), CHAR(10))
[Column Numbers No] =JOIN(COLLECT(INDEX({sheet1 Col1:Col180}, 1), INDEX({sheet1 Col1:Col180}, [Row in sheet1]@row ), "No"), CHAR(10))
[Column Numbers N/A] =JOIN(COLLECT(INDEX({sheet1 Col1:Col180}, 1), INDEX({sheet1 Col1:Col180}, [Row in sheet1]@row ), "N/A"), CHAR(10))

If you refer to Sheet1 below, to locate the Yes columns, we understand that we need to obtain the column numbers for 'Yes', such as 1, 3, 7, and so on, for the ID 'Test Case'.

To do that, we need to obtain the Test's Row number and use it to retrieve the row's Col1:Col180 range.

The following formula does the first.

[Row in sheet1] =IFERROR(INDEX({sheet1 : Row}, MATCH(ID@row , {sheet1 : ID}, 0)), "")

And, the following formula does the second.

INDEX({sheet1 Col1:Col180}, [Row in sheet1]@row )

Using the range obtained by the INDEX as the condition range of the COLLECT function, we can get numbers such as 1, 3, 7, and so on, using the following formula.

[Column Numbers Yes] =JOIN(COLLECT(INDEX({sheet1 Col1:Col180}, 1), INDEX({sheet1 Col1:Col180}, [Row in sheet1]@row ), "Yes"), CHAR(10))

Then, finally, using the obtained numbers column's values as the HAS function's search_range, we can collect Sheet 3's Column values using the sheet's Row range as a criterion, in the following formula;

[Yes Columns] =JOIN(COLLECT({sheet3 : Column}, {sheet3 : Row}, HAS([Column Numbers Yes]@row , @cell )), CHAR(10))

Note, in the above formula, {sheet3 : Row} is number 1 to 180, and the HAS function checks if [Column Numbers Yes]@row (for example, 1, 3, 7, and so on) has 1 or 2 or 3 or, so on, and if has, return the corresponding Col1, Col3, and so on.

Sheet1:

• Each row represents an ID and has values like "Yes", "No", or "N/A" across 180 columns (Col1 to Col180).

https://app.smartsheet.com/b/publish?EQBCT=5815c18046844c3fa571c0e2b8d2a429

Sheet3 (Column Mapping Helper):

• This sheet maps Row (1–180) to corresponding column names (Col1 to Col180).
• It is used as a lookup table to translate column numbers into column names.