Hi, decided to ask this a separate question, ...

[Alper]

Hi, decided to ask this a separate question, in the following line;

[Startdate] <= [THISROW].[Startdate]

Is it possible to modify right part to refer another sheet? Tried:

sheet1[THISROW].[Startdate] sheet1.[THISROW].[Startdate] [THISROW].sheet1[Startdate] …etc, many other forms of it. All failed and there’s no such example on web either. Any opinions?

[Alper]

@Harry

Hi, can you please verify, if the following notation is correct:

([cur_str_date] >= ANY(SELECT(sheet1[startdate], ISNOTBLANK(sheet1[startdate]))))

Appears correct but did not work for me, thinking on switching to lookup().

Also wondering how to test output of such a line. App formula of a virtual column of LIST type ? I am using it in validif(), so cannot visualize the output. Thanks !

Edit: I tried to modify as following; (Setsheet[cur_str_date]… And it complained about cannot compare a list with another list. Is this the problem? Setsheet has only one row, after column names.

[Harry2]

@Alper If you want to reference cells from a different table, you need to use SELECT() or LOOKUP(). In the case of SELECT(), you will need to wrap the output in the ANY() expression like so: ANY(SELECT(…)). This is because the output of SELECT() is a list of values, not a single value. More information here:

help.appsheet.com - LOOKUP()

https://help.appsheet.com/expressions/functions/select LOOKUP() help.appsheet.com

[Steven_Coile]

Within a SELECT() condition, all column references are relative to the table indicated in the first argument to the SELECT(). In your expression, the first argument is sheet1[startdate], which references the table, sheet1. So within the condition, column references will be columns of sheet1.

So your expression should be:

([cur_str_date] >= ANY(SELECT(sheet1[startdate], ISNOTBLANK([startdate]))))

[Steven_Coile]

SELECT(sheet1[startdate], TRUE) generates a list containing every value in the startdate column of the sheet1 table. ANY(…) then gets one arbitrary value from that list. In practice, ANY() grabs the first value, but SELECT() may return its list in a random order, so ANY(SELECT(…)) returns one arbitrary value from the values selected.

Consequently, you’re comparing cur_str_date and cur_end_date against arbitrary and effectively meaningless values.

[Alper]

+Steve Coile

I have searched possibilities on using other functions but it seems its not possible to refer all rows of a different table individually for comparison purposes (may be until someone invents [_THATROW] parameter ) Decided to use your perfect formula by moving setSheet’s columns into main data table. Thank you all for your patience.

[Alper]

+Steve Coile I got that point, thank you. Meanwhile, I work hard and created the following validif() condition, its placed in the sheet where cur_str_date and cur_end_date exists, and I really dont understand why its not functioning… (ISNOTBLANKs are replaced with TRUE, I hope its fine).

=AND(

([cur_str_date] < [cur_end_date]),

OR(

AND(

([cur_str_date] >= ANY(SELECT(sheet1[startdate], TRUE))),

([cur_str_date] <= ANY(SELECT(sheet1[enddate], TRUE)))

),

AND(

([cur_end_date] >= ANY(SELECT(sheet1[startdate], TRUE))),

([cur_end_date] <= ANY(SELECT(sheet1[enddate],

TRUE)))

)

AND(

([cur_str_date] < ANY(SELECT(sheet1[startdate], TRUE))),

([cur_end_date] > ANY(SELECT(sheet1[enddate], TRUE)))

)

) )