Apr 16, 2018 12:23 PM
Hi -
I am trying to make a formula that will indicate if Columns A, B and C are not blank, then Column D would be filled in. I was able to get it to work with the simple formula of one column, but can’t figure out how to write it to check multiple columns.
What I’m trying to get it to do:
IF
{Event Name} = “” and
Location = “” and
{Start Date} = “” and
{End Date} = “”
then put “Send” in formula column.
Thanks in advance - would love help in learning how to write this out properly.
Apr 16, 2018 12:40 PM
So, if all 4 of the fields are empty, then the word “SEND” needs to be displayed in the new formula field.
That would be:
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)
Apr 16, 2018 09:33 PM
Jeannie -
One minor change to what @Andre_Zijlstra provided: The next to last line should read "SEND"
and not “SEND”. (The difference is subtle: Look at the style of quotation marks around the word.) This is only a problem if you mark-and-copy his formula; if you were to retype it, it would work fine.
If you precede and follow your formula text with three backticks (aka accent grave
), as so —
```
— both the correct quotation style and indentation are preserved:
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)
Apr 16, 2018 10:58 PM
@W_Vann_Hall
Thank you for taking time for this topic (again). It seems my Dutch keyboard could be reason for some confusion here.
This Quotes key:
is left of the key. I tried both options in the formula (“SEND”, and ‘SEND’) and both rendered the same result.
Another key (left of the <1>:
did not work. This seems to be the “Grave” key? And that is the one I should use preceding and following the formula text?
So, the backtick is not to be used in the formula text, but preceding and following the formula.
In the formula I should use the ’ keystroke in stead of "?
Apr 16, 2018 11:23 PM
On European keyboards, the grave
key is usually a dead key, used as the first stroke in a two-key sequence: [grave]+e = è. In such cases, there is a way to generate the grave character by itself, either [grave][grave] or [grave][spacebar], if I recall correctly.
In Markdown (which this forum uses), you can trigger code mode one of three ways:
Unfortunately, method 1 does not support indentation, as multiple space characters are rendered as a single space. (I’d through method 3 did not, as well, but this proved wrong.) For instance, all three of the following examples are the same, except for how code mode is triggered:
Single backtick before and after each line
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)
Four spaces before each line
(Note, there must be a blank line before the first code line.)
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)
Three backticks before and after excerpt
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)
As you can see, the final method places the code excerpt in its own scrollable window and performs syntax-specific highlighting. (Based on what language, I’m not certain; most Markdown implementations don’t highlight unless a language is specified after the first set of backticks.)
If you can’t tweeze a backtick character out of your keyboard, you can use the four-space method of triggering code mode.
Here is a handy Markdown reference.
Edit: Well, actually you can’t see the third method places the code in its own scrollable window, as the code excerpt wasn’t long enough.
Apr 17, 2018 03:28 AM
Thank you. The cheatsheet helped a lot as well.
Apr 17, 2018 09:26 AM
Thank you so much for this. Very simple and easy to understand. I really appreciate your help.
If I wanted to swap this around, so the formula is if those columns are NOT blank how would I do that?
Apr 17, 2018 09:27 AM
Thank you for the formula! Much appreciated and worked great with the modification mentioned below.
Apr 17, 2018 10:23 AM
If I wanted to swap this around, so the formula is if those columns are NOT blank how would I do that?
You could use
IF(
AND(
{Event Name} != BLANK(),
{Location} != BLANK(),
{Start Date} != BLANK(),
{End Date} != BLANK()
),
"SEND"
)
or
IF(
NOT(
OR(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
)
),
"SEND"
)
Either should do.