If user deletes 1st row, he loses array formula

Ask for Help
1

i have a button that links to a sheet where many products are listed. the user can edit and delete these products. in my google sheet, there are array formulas in many of the columns. if the user decides to delete the 1st product, he loses the information of that column for all the other products. is there a workaround? or am i being dumb?

2 Likes
2

A trick to make this work (until we support formulas in Glide) is to write all of your formulas in an empty first row. Glide will skip the empty row, so nobody can edit or delete it.

Check out how we do it in the Issue Tracker template (sheet):

image
image.png1640Ă—826 109 KB

4 Likes
3

You can write the following formula in the first row:
ARRAYFORMULA(IF(ROW(A:A)=1,“column_title”,IF(ISBLANK(A:A),your_formula)))

2 Likes
5

This is what I do… if the ColumnName is “Price”, you can have the first row as…

={“Price”;ARRAYFORMULA(…your logic…)}

This will have the Header name as “Price” and it will also hold the Arrayformula logic too.

5 Likes
6

Besides backups of the spreadsheet, for most complex arrayformulas I copy the text of them to the cell notes as a quick backup that can easily be copy and pasted back into the cell.

3 Likes
7

I’m new to array formulas in Google Sheets, but I just copied a useful one from these forums into my spreadsheet. I did this by clicking the column letter to select the entire column, and then holding down the command-key on my mac to un-select ONLY the header cell. Now the formula is applied to every current/future row of that column except for the header.

Is this also a good solution OR is this not a best-practice for reasons I’m not aware of? Thanks!

8

You could try starting the arrayformula in the header of your spreadsheet.
={“YourHeader”;arrayfromula…}

Here is another example

Summary

={“YourHeaderHere”;arrayformula(if(isblank(A2:A),"",(NamedRange-7)))}

Note: Don’t forget to add the } at the end of the formula :slight_smile:

4 Likes
9

Just curious what the formula looks like that you created. Maybe I’m reading it wrong, but it seems like you replicated a formula to every row in in that column. An arrayformula is a single formula in a single row that automatically applies to all following rows in that column. @Eric_Penn’s advice is good to follow because by combining the header and formula into single cell in the very first row, you eliminate the possibility of someone deleting a row, which could cause the arrayformula to be deleted if it’s in row 2.

If you share the formula you used, then we can make sure the range of rows specified in the formula is an endless range.

5 Likes
10

Eric has nailed the point about having the formula in the header so it won’t interfere with your second row.

If you want to read more about arrayformula then I had a post here.

3 Likes
11

Hi Jeff, it was:

=ARRAYFORMULA(IF(D2:D<>"",LOWER(D2:D),""))

I needed it to solve the issue of Glide being case-sensitive with emails used in relation look-ups. I think I understand now - I basically copied a formula to multiple cells that already affects multiple cells all on its own when listed once. I’ve just moved it into the header row, that works perfectly! I had a feeling I needed to learn something here :relieved:

Thanks All!

3 Likes