How to update last chosen option from a dynamic dropdown list?

[Anthoney]

Hi

I’m trying to create a schedule for persons worldwide (S1:W24).
In order for everyone to be able to see the times in their local time zone, I’ve made a basic schedule with the times in UTC+00:00 (M1:Q24) and a dropdown list with all the different time zones (T26:W27).

In the columns T3:T24 the times are calculated based on the offsets of the chosen time zone with the formula:

=IFERROR(N3+INDEX($J$2:$J$97,MATCH($T$26,$K$2:$K$97,0)),"")

Since times can be influenced by Daylight Saving Time (DST), apart from the standard range with time zones (A1:C97) I’ve created a second range in which I added 1 hour to all the time zones (E1:G97) and made a “YES”/“NO” dropdown list (AA26:AA27).
And I’ve created a third range (I1:K97) based on the “YES” or “NO”, which determines how the dropdown list looks like:

K2=IF($AA$26="NO",$C2,$G2)

The thing is, when I switch the DST from “YES” to “NO” or vice versa, the last chosen time zone in T26:W27 doesn’t get updated even though the underlying dropdown list is.
See screenshot:

The last chosen time zone was (UTC+02:00) Amsterdam… , which is the time zone during DST, and after switching the DST to “NO” the dropdown list gives the option (UTC+01:00) Amsterdam…
So the options in the dropdown list are good, it’s only that the previously chosen option isn’t updated according to the list.
Is there any way to do this?
I’ve tried several INDEX/MATCH formulas in Data>Validity>Custom instead of the Cell range, but I can’t make it work.
Can anyone please help me?

Thank you very much in advance?
TimeZone Schedule.ods (33.4 KB)

[mariosv]

Because the selection comes from Data/Validity, it is a text, only calculated when you make a selection on the drop-down.

[Anthoney]

Thank you @mariosv for your reply.
Isn’t it possible via Data>Validity>Custom to do it with a formula?

[mariosv]

Yes, but you need to select the drop-down, to update the formula in data validity.

[Anthoney]

Okay, thank you, Miguel

Does anyone know how to solve this problem?

[mariosv]

use the formula needed in your case.

[Anthoney]

Excuse my ignorance, @mariosv, but I can’t make your suggestion work nor do I understand how it could be helpful.
I don’t want to add “1” to column K2:K97 or to C2:97 for that matter, because the adding/subtracting depends on the DST “YES”/“NO”.
And this works fine.
The only thing that I want is that when the last chosen option in T26:W27 comes from K37, and when K37 changes, that T26:W27 is updated accordingly.
In case that this cannot be achieved via a Data>Validity dropdown list, is there another way to make it work?

[Anthoney]

I’ve tried by determining the address from where T26:W27 comes from with the following array formula in K100

{=TEXTJOIN(";",1,IF($K$2:$K$97=$T$26,ADDRESS(ROW($K$2:$K$97),COLUMN($K$2:$K$97),4)))}

And then to display the findings in T28 with:

=INDIRECT(K100)

so that the offsets in T3:T24 are based on T28 instead of T26.
This works uptil the DST changed from “NO” to “YES” and vice versa.

So “behind the scenes” of T26:W27 something changes, but visually it doesn’t.
TimeZone Schedule_v1.1.ods (34.2 KB)