Is it possible to have a cell filled in with information if left blank on a form?

Options

I am hoping to use an existing sheet with a new form to gather information. Currently, people should be completing certain fields on their own. What I would like to do, is that if they leave it blank (or by design, they don't see the field so it is left blank intentionally), I could use a formula to fill in information from another cell on the sheet. If they did complete the field, I don't want to override that information though. In the image below, the locator number column would be a field that would appear on one form to and people should manually enter a number there. Ideally, we would like to give a specific target audience a different form link. Based off of their response for APC Session Title, a specific APC Locator Number is pulled through using VLOOKUP. I would like to see if the number that pulled through into the APC locator number column could be entered into the Locator Number column only if it was blank. Is this even possible and if so, could someone help with the formula?


Best Answers

Answers

  • Kate Kelley
    Kate Kelley ✭✭✭✭
    Options

    Thanks! You wouldn't happen to have any idea what that formula would look like? I'm trying to piece it together and keep getting incorrect arguments

  • Kate Kelley
    Kate Kelley ✭✭✭✭
    Options

    YOU ARE BOTH MY HEROES!!! THANK YOU!!! Worked perfectly. I was definitely overengineering the formula.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Kate Kelley

    Glad to hear it worked for you!! 🙂

  • Kate Kelley
    Kate Kelley ✭✭✭✭
    edited 10/15/20
    Options

    @Genevieve P Is it possible to have it look at multiple columns. I'm assuming this is an IF(AND) formula . Below is what I'm trying, but only getting Unparseable errors. So if the locator column was empty it would look to ASN locator number. If that was empty, it would then look at the TAX locator number.

    =IF([Locator Number]@row = "", [ASN Locator Number]@row, (IF(AND[ASN Locator Number]@row = "", [TAX Locator Number]@row, [Locator Number]@row)))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Kate Kelley

    You're correct, you can use an IF(AND statement for this! You'll want to put this statement first, since logic statements read left-to-right and will stop as soon as it has an answer.

    For an AND statement, you want to place your two criteria (between these), like this:

    AND([Locator Number]@row = "", [ASN Locator Number]@row = "")

    Then you can put that in an IF statement:

    IF(AND([Locator Number]@row = "", [ASN Locator Number]@row = ""), [TAX Locator Number]@row


    Now you'll want to NEST those two IF statements together.

    FULL FORMULA:

    =IF(AND([Locator Number]@row = "", [ASN Locator Number]@row = ""), [TAX Locator Number]@row, IF([Locator Number]@row = "", [APC Locator Number]@row, [Locator Number]@row))


    This first looks to see if both [Locator Number] and [ASN Locator Number] in this row are blank, and if they are, returns [TAX Locator Number]. However, if either of these cells have content in them and are not blank, it will move on to your second statement. This says if just the [Locator Number] in this row is blank, return the [APC Locator Number]. Otherwise, if the [Locator Number] is not blank, return the [Locator Number] in this row.

    Does that make sense? Let me know if it works for you!

    Cheers,

    Genevieve