In the second sheet, I want to be able to create a field where Email is referenced, but filtered to only show emails that have a date, and the value 0.
So in this case, I’d get a short list of around 8 emails, where the value is 0, and there is a date.
I tried first to create a second widget to sheet 1 where the emails are pre-filtered so I simply need to reference this widget:
The problem is that I don’t know how to create a reference to THIS widget, rather than the other one.
Is what I am after possible? Am I over complicating this?
EDIT: The above seemed difficult to understand. So to explain it differently. I want a dropdown list of emails that have a date, and the value 0.
So when I click to select an email, the list only contains emails that fit that criteria rather than all emails.
" You can accomplish that today by adding to the Employees table another column, say, Name_If_Manager with a formula like $Full_Name if $Role == "manager" else "". This will duplicate the Full_Name column but only for managers.
Then in the Projects table, set your Managers column to point to table Employees and for “SHOW COLUMN” select Name_If_Manager. This will limit the dropdown to non-empty values, which happen to be only the managers."
so here is your first table.
check the formula… when there is a date and check is 0, then it is the email, else is blank.
SO only where your conditions are ok, it will repeat the email.
So, this solution kind of works. It’s not perfect but it’s good enough!
So, essentially, TimesUsed is the number of times emails showing in Table2.
With this in mind, the moment the email is selected from the list, it will disappear from the list… Thus removing it again.
So my solution is to create… Another column.
Originally it was a checkbox, but there might be some time in the future where I might use an email multiple times, so I wanted to have it counter just to future proof it
Honestly it surprised me but it “just works”. I fully expected it wouldn’t work since the $ChooseEmail is Blank. Yet, selecting something from there updates its value to the last chosen email. Despite it showing as blank. No need to do anything besides selecting the email via $ChooseEmail and it all updates correctly.