Autocomplete of cells

[Antonoff]

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.

[Rosewebstudio]

You could use set columns

https://docs.glideapps.com/all/reference/actions/single-actions/data/set-columns

Or maybe if then else

If → Then → Else Column

[Antonoff]

Thanks!
And in google tables?

[Rosewebstudio]

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

I can’t take credit for this tip but I just found out about this trick/tip by reading a post here. 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…

[ThinhDinh]

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

[Darren_Murphy]

Are you looking for ARRAYFORMULA() ?

[Antonoff]

It’s solve don’t work for formulas

[ThinhDinh]

Try putting this in D1:

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

[Antonoff]

[Darren_Murphy]

Can you provide an english translation of that error message?

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

[Antonoff]

@ThinhDinh

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

Sorry) “Syntax error in formula”

[Darren_Murphy]

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

You need to remove that.

[Antonoff]

@ThinhDinh

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

The parenthesis is automatically added

[Darren_Murphy]

@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)))}

[Antonoff]

@Darren_Murphy

ut it inside the curly braces, like this:

I do exactly this, but the parenthesis still appears

[Darren_Murphy]

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:

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

[Antonoff]

@Darren_Murphy

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

yep,i’m already do it

[Darren_Murphy]

So it’s working now?

[Antonoff]

Yes, big thanks

[ThinhDinh]

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