Working on a check in app for our activations. Have 3 smartsheets:
For the bunk number list we want to only show bunks based on power need, and preference of top/bottom bunk.
Is there a way to set this up so that the list of bunks filters first for power (y/n), then top/bottom (y/n), while at the same time only showing bunks not already assigned?
Iโve been able to use the data validity successfully for assigned/not assigned - but not three items like this. Any suggestions would be greatly appreciated. Theyโve given me until Wednesday to come up with version1 , yay?!
Maybe I got you use case wrong, but from what i read, why donโt you just use a slice to filter only โnot assignedโ and the sort by what ever columns you want?
@tcanelli
Is that BunkID a ref column? If yes, it will be easy with ORDERBY(โฆ)
ORDERBY(
SELECT(
TableName[Bunk ID],
AND(
[Power Accessible]="Yes",
OR(
[Bunk Position]="Top",
[Bunk Position]="Bottom"
),
[Assigned]="No"
)
),
[Power Accessible],TRUE,
[Bunk Position],TRUE
)
OR you can simply use a FILTER(โฆ) function in the Valid_if:
ORDERBY(
FILTER(
"TableName",
AND(
[Power Accessible]="Yes",
OR(
[Bunk Position]="Top",
[Bunk Position]="Bottom"
),
[Assigned]="No"
)
),
[Power Accessible],TRUE,
[Bunk Position],TRUE
)
This expression is suitable for use in Valid_If to control the drop-down menu for the Bunk ID column:
SELECT(
Bunk number list[Bunk ID],
AND(
NOT([Assigned?]),
([Power Accessible] = [_THISROW].[Power Accessible]),
([Bunk Position] = [_THISROW].[Bunk Position])
)
)
SELECT(Bunk number list[Bunk ID], ...)
gathers a list of Bunk ID column values from rows of the Bunk number list table that match the given criteria (...
; see below).
AND(..., ..., ...)
matches only those rows that match all of the given criteria (see below).
NOT([Assigned?])
limits the identified bunks to only those that arenโt currently assigned. You didnโt indicate how a bunk assignment is indicated, so Iโve assumed a column named Assigned? of type Yes/No. Adjust this expression as appropriate.
([Power Accessible] = [_THISROW].[Power Accessible])
further limits the results to only include those rows with a Power Accessible column value that matches that in the form for the new row.
([Bunk Position] = [_THISROW].[Bunk Position])
further limits the results to those rows with a Bunk Position value that matches the formโs.
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |