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

Kate Kelley

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?

Purnima Gore

Hi

You could use two additional fields to do this.

Column 1 is where they enter the data

Column 2 has the information you want to add

Column 3 is the one you use for the Location Number

You can use the IF Formula in Column 3, so that if the data is entered in Column 1 then Column 3 = data in column 1 and if not then choose what is in column 2.

Hope that helps.

Kind regards

Purnima

Genevieve P.

Hi @Kate Kelley,

Here's an example of @Cierr 's solution:

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

You would need to have this in a third, separate column. This is an IF statement that says, if the Locator Number cell in this row is blank (or is ""), then return the APC Locator Number from this row. Otherwise, if it's not blank, then return the Locator Number.

See the Help Center article that goes through how to use the IF Function, here.

You may also be interested in reviewing the recorded webinar on formulas, here.

Cheers!

Genevieve

Purnima Gore

Hi

You could use two additional fields to do this.

Column 1 is where they enter the data

Column 2 has the information you want to add

Column 3 is the one you use for the Location Number

You can use the IF Formula in Column 3, so that if the data is entered in Column 1 then Column 3 = data in column 1 and if not then choose what is in column 2.

Hope that helps.

Kind regards

Purnima

Kate Kelley

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

Genevieve P.

Hi @Kate Kelley,

Here's an example of @Cierr 's solution:

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

You would need to have this in a third, separate column. This is an IF statement that says, if the Locator Number cell in this row is blank (or is ""), then return the APC Locator Number from this row. Otherwise, if it's not blank, then return the Locator Number.

See the Help Center article that goes through how to use the IF Function, here.

You may also be interested in reviewing the recorded webinar on formulas, here.

Cheers!

Genevieve

Kate Kelley

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

Genevieve P.

Hi @Kate Kelley

Glad to hear it worked for you!! 🙂

Kate Kelley

@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.

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