IF Formula - If yes, run this formula. If not, enter nothing.

mhawkins

Hello all! I have been working through part of a larger solution, but have one little tweak to work out. I have longer multi-select options in another column, and am using a formula to shorten their names from another list to 6 value codes, as you see in column "Short Version 1". Because there are often tons and names are long, I have 4 additional columns and extensions of the formula to the right of "Short Version 1".

I would like to write a formula (that can become a column formula) that is sort of "If column [count FRTs] = 0, then don't perform this larger formula, but If column [count FRTs] >= 1, then perform this formula (which starts with an INDEX(COLLECT).

As a background, [count FRTs] is counting the number of our longer option names. This is a form-populated sheet, and some entries do not have the longer names in there. So essentially I would like to run the formula only on the rows that have the longer names (signified by the [Count FRTs] being 1 or more), and if not, then to leave it blank. (The goal of this all being to get rid of the "#Invalid Value" responses in the rows with yellow.

Can anyone help me with some If/Then logic? I struggle with it. Thank you!!

KPH

In the column that you want the result to appear, type

=IF([Count FRTs]@row>=1,

Then enter your longer formula (without an equals sign)

Than add a closing parenthesis: )

The full formula will look like this (where the part in bold is your Index Collect

=IF([Count FRTs]@row>=1, INDEX(COLLECT([column]:[column], [criteria]:[criteria], true), 1)

KPH

In the column that you want the result to appear, type

=IF([Count FRTs]@row>=1,

Then enter your longer formula (without an equals sign)

Than add a closing parenthesis: )

The full formula will look like this (where the part in bold is your Index Collect

=IF([Count FRTs]@row>=1, INDEX(COLLECT([column]:[column], [criteria]:[criteria], true), 1)

mhawkins

This worked perfectly - thank you!! I have one more piece to unlock and tagged you in a different thread. Thank you so much for all of the help!

KPH

That is good to hear, thanks for letting me know. I will get back to you on the other request when I have time to read it through properly - am jumping between meetings today.