# Autocomplete of cells

**URL:** <https://community.glideapps.com/t/autocomplete-of-cells/24283>\
**Category:** Ask for Help\
**Created:** [March 15, 2021, 11:35am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283 "2021-03-15T11:35:14Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 15, 2021, 11:35am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/1 "2021-03-15T11:35:14Z")

</div>

Hi there!  
Can’t find the answer: how to set up autocomplete of cells in a column if a new row appears? You need to have a formula in each new cell in the column. Tried it through isblank, but the formula doesn’t work.

---

<div class="post-metadata">

**Author:** ![Rosewebstudio](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosewebstudio/32/12261_2.png) [@Rosewebstudio](https://community.glideapps.com/u/Rosewebstudio)\
**Post date:** [March 15, 2021, 11:37am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/2 "2021-03-15T11:37:58Z")

</div>

You could use set columns

[https://docs.glideapps.com/all/reference/actions/single-actions/data/set-columns](https://docs.glideapps.com/all/reference/actions/single-actions/data/set-columns)

Or maybe if then else

> **[If → Then → Else Column](https://docs.glideapps.com/all/reference/data-editor/computed-columns/if-then-else)**
>
> Compute different values depending on other data in your sheet

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 15, 2021, 11:40am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/3 "2021-03-15T11:40:26Z")

</div>

Thanks!  
And in google tables?

---

<div class="post-metadata">

**Author:** ![Rosewebstudio](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosewebstudio/32/12261_2.png) [@Rosewebstudio](https://community.glideapps.com/u/Rosewebstudio)\
**Post date:** [March 15, 2021, 12:00pm UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/4 "2021-03-15T12:00:19Z")

</div>

Quick example

=IF(ISBLANK(M2:M),“Delivery”,“Delivered”)

M2:M is the range

“Delivery” if value if true

“Delivered” if value is false

Read this if you want to put an array in the column header. This will auto populate any new rows added to your sheet

> [@Using ARRAYFORMULA()'s in Row 1 of your Spreadsheet](https://community.glideapps.com/t/using-arrayformula-s-in-row-1-of-your-spreadsheet/529):
>
> I can’t take credit for this tip but I just found out about this trick/tip by reading a post [here](https://community.glideapps.com/t/if-user-deletes-1st-row-he-loses-array-formula/246). So the “Normal” way of using an ARRAYFORMULA function is to use it in row 2 of a given column. You just have to be careful not to accidentally deleted it, or have a user delete it. So the next idea was to put it in row 2 but not put any other data in the row and then hide or minimize that row. Basically your data for the non ARRAYFORMULA columns would start on row 3. Then I saw the post I’ll l…

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [March 15, 2021, 12:03pm UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/5 "2021-03-15T12:03:54Z")

</div>

Also make sure you use arrayformula to fill the whole column.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 15, 2021, 12:12pm UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/6 "2021-03-15T12:12:30Z")

</div>

Are you looking for [ARRAYFORMULA()](https://support.google.com/docs/answer/3093275?hl=en) ?

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 15, 2021, 7:16pm UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/7 "2021-03-15T19:16:27Z")

</div>

It’s solve don’t work for formulas  
 ![formula](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/b/fbc0b7d548a149eff4e8b9f9dc4b59285a6ecfa7.png)

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [March 15, 2021, 11:45pm UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/8 "2021-03-15T23:45:07Z")

</div>

Try putting this in D1:

`={"Country Check";ARRAYFORMULA(IF(C2:C="","",IF(C2:C=Feed!C2:C100,1)))}`

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 4:44am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/9 "2021-03-16T04:44:50Z")

</div>

![error](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/9/c9d59ca1722ac88d75931e799bfb06851e477817.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 16, 2021, 4:58am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/10 "2021-03-16T04:58:12Z")

</div>

Can you provide an english translation of that error message?

(Are there any values in cells D2 to D100?)

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 4:59am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/11 "2021-03-16T04:59:37Z")

</div>

> [@ThinhDinh](#):
>
> ={“Country Check”;ARRAYFORMULA(IF(C2:C=“”,“”,IF(C2:C=Feed!C2:C100,1))}

Sorry) “Syntax error in formula”

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 16, 2021, 5:04am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/12 "2021-03-16T05:04:02Z")

</div>

ah, in the screen shot you provided you have an extra parentheses “)” at the end…

 ![Screen Shot 2021-03-16 at 1.02.52 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/b/c/bce22c27066aad2837c946adb64cc3b45b53466c.png)

You need to remove that.

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 5:09am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/13 "2021-03-16T05:09:12Z")

</div>

> [@ThinhDinh](#):
>
> ={“Country Check”;ARRAYFORMULA(IF(C2:C=“”,“”,IF(C2:C=Feed!C2:C100,1))}

The parenthesis is automatically added

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 16, 2021, 5:12am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/14 "2021-03-16T05:12:09Z")

</div>

> [@ThinhDinh](#):
>
> ={“Country Check”;ARRAYFORMULA(IF(C2:C=“”,“”,IF(C2:C=Feed!C2:C100,1))}

Put it inside the curly braces, like this:

`={“Country Check”;ARRAYFORMULA(IF(C2:C="","",IF(C2:C=Feed!C2:C100,1)))}`

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 5:33am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/15 "2021-03-16T05:33:39Z")

</div>

> [@Darren\_Murphy](#):
>
> ut it inside the curly braces, like this:

I do exactly this, but the parenthesis still appears

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 16, 2021, 5:49am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/16 "2021-03-16T05:49:24Z")

</div>

Check the quotes around “Country Check” at the start of the formula. It looks like they are being converted to “smart quotes”. The following (slightly modified version) works for me:

```auto
={"Country Check";ARRAYFORMULA(IF(C2:C="","",IF(C2:C=D2:D,1)))}

```

 ![Screen Shot 2021-03-16 at 1.48.50 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/1/d167cf7ebea859279b5e5fe4fb9c49e67f1ad41c.png)

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 5:53am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/17 "2021-03-16T05:53:16Z")

</div>

> [@Darren\_Murphy](#):
>
> ={“Country Check”;ARRAYFORMULA(IF(C2:C=“”,“”,IF(C2:C=Feed!C2:C100,1)))}

yep,i’m already do it

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 16, 2021, 5:54am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/18 "2021-03-16T05:54:24Z")

</div>

So it’s working now?

---

<div class="post-metadata">

**Author:** ![Antonoff](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antonoff/32/22045_2.png) [@Antonoff](https://community.glideapps.com/u/Antonoff)\
**Post date:** [March 16, 2021, 5:55am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/19 "2021-03-16T05:55:01Z")

</div>

Yes, big thanks

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [March 16, 2021, 8:26am UTC](https://community.glideapps.com/t/autocomplete-of-cells/24283/20 "2021-03-16T08:26:54Z")

</div>

Missed an extra paranthesis in my original code, thanks for the fix 😉

[Next page](https://community.glideapps.com/t/autocomplete-of-cells/24283.md?page=2)