Solved: Ordering a dropdown that has valid_if - Page 2

Pratyay_Rakshit · 04-19-2020 06:50 AM

I have a valid_if expression using select() that drops down a list from another spreadsheet.

What I want to do is sort the dropdown based on a column value, preferably the ‘date’.

I tried wrapping up the select expression with orderby() using the [Date] column, but I get shown a message that orderby doesn’t have the valid inputs.

I saw that an exact same problem was posted by some other person, the solution to which was suggested as:

Try generating a sorted list using the Suggested values property for the column.

which seemed to have worked for the seeker.

But I don’t know how to properly write the expression in the suggested values field.

The select expression in valid_if :

select(RMC Bill entry[Computed], and(([Which Company?]=[_THISROW].[RMC Supplier]), [Paid?]=“Unpaid”))

I tried using orderby(select(RMC Bill entry[Computed], and(([Which Company?]=[_THISROW].[RMC Supplier]), [Paid?]=“Unpaid”)), [Date]) to no avail.

The [Date] column is within the table RMC Bill entry.

Steve

If you have a virtual column (named Rows by Date, e.g.) with the needed rows ordered in the desired way:

ORDERBY(
  FILTER(
    "RMC BILL entry",
    AND(
      ([Which Company?] = [_THISROW].[RMC Supplier]), 
      ([Paid?] = “Unpaid”)
    )
  ),
    [Date]
)

Theoretically, you could get the desired-order list of column values using a bulk dereference:

[Rows by Date][Computed]

(Note there is no period between the two column references.)

I say “theoretically” because I don’t have enough experience with bulk dereferences to say with confidence how they behave, but I would expect this to work.

View solution in original post