Diminishing Lists for valid if across multiple fields

[Erika_Braxton]

hi, so i have a valid if formula that is a select() but there are multiple fields where these values can be used, but we absolutely to not want a value duplicated in the other fields. i had help constructing this many months ago, but i find it does not work now, and i have been working a few days on this problem, many, many different versions and attempts at these formulas:
field [Buffer 1]: SELECT(RSP BUFFERS[GESTL1], ([SHORTPART] = [_THISROW].[ITEM]))
field [buffer 2]: SELECT(RSP BUFFERS[GESTL1], ([SHORTPART] = [_THISROW].[ITEM]))-LIST([_THISROW].[BUFFER 1])
field [Buffer 3]: SELECT(RSP BUFFERS[GESTL1], ([SHORTPART] = [_THISROW].[ITEM]))-LIST([_THISROW].[BUFFER 1],[_THISROW].[BUFFER 2])
etc.

[Steve · Accepted solution]

[_THISROW] should only be dereferenced within a SELECT()(-ish) expression; avoid using it otherwise.

SELECT(RSP BUFFERS[GESTL1], ([_THISROW].[ITEM] = [SHORTPART]))

SELECT(RSP BUFFERS[GESTL1], ([_THISROW].[ITEM] = [SHORTPART])) - LIST([BUFFER 1])

SELECT(RSP BUFFERS[GESTL1], ([_THISROW].[ITEM] = [SHORTPART])) - LIST([BUFFER 1], [BUFFER 2])

[Erika_Braxton]

thank you steve, i will give this a try, i think the reason [_thisrow] was there was because i didnt want it to subtract the values from entire column of the sheet, just the other values from this one record.