Substituting a value from another column


Was looking for a way to utilise data substitution, when a user selects data from one column but the App inputs data from another.

Say we have the following schema:

Two Sheets.
One for data to be fed to DropDown [ColumnB];
One where User inputs are stored [ColumnA].

User selects a value from a list, fed from Column B.
VALID_IF $DataSheet[ColumnB]

What I need is when Item Aaa is selected, the value 1 is recorded in the UserSheet[ColumnA].
Tried something like:
SELECT($DataSheet[ColumnС], $DataSheet[ColumnB] = UserSheet[ColumnA], TRUE)
however, not successful.
Would appreciate a hint on this.
Thank you,

Can you elaborate on how you tried this? In what way was it unsuccessful?

Apologies for delayed response.

Firstly, tried to create an Action - Data: set the values, which just did not do anything - perhaps did not link it properly.
Then, considering the drawing above, in UserSheet[ColumnA] Initial Value = SELECT($DataSheet[ColumnС], $DataSheet[ColumnB] = UserSheet[ColumnC], TRUE)

Well it just returned


instead of


I’m surprised you got a result at all.

$DataSheet[ColumnB] is a column expression. It will produce a List containing the values of the ColumnB column in the $DataSheet table.

Likewise, UserSheet[ColumnC] will produce a List of the ColumnC values in UserSheet. Note that your diagram does not show a ColumnC in UserSheet.

Comparing two Lists is possible: each is converted to text and compared as such. The only way that comparison would be TRUE is if each list contained the same number of items in the same order. If you meant UserSheet[ColumnA] rather than UserSheet[ColumnC] and the graphic reasonably represents the similarities and differences between the lists, the items from $DataSheet[ColumnB] are not at all equal to those from UserSheet[ColumnA], so the expression should always be FALSE. Given that, SELECT() should have returned nothing.

Thank you, will re-think the question.

1 Like