Is it possible to have a cell filled in with information if left blank on a form?
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
-
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
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
YOU ARE BOTH MY HEROES!!! THANK YOU!!! Worked perfectly. I was definitely overengineering the formula.
-
Hi @Kate Kelley
Glad to hear it worked for you!! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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)))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives