I've spent many hours creating and modifying a spreadsheet only to have a user delete my formulas and then wonder why it just doesn't work anymore. I've tried protecting cells but then they get angry as to why I don't let them touch cells so I unprotect...and then they delete stuff.
And then I give them one of these:
I've been trying a new way...hiding the formula(s) in the headings of a table. So far, no one deletes a header and when they do something strange, the whole column goes blank and they "undo" or just email me instead of just carrying on and then wondering why it doesn't work.
The template for the formula is this:
={"<header>";formula}
It seems pretty simple and the ";" makes sure the formula appears in the cell below the header. Want to go beside? Try using "," instead.
So here is my example to show how it works. I have a list of 185 names (thanks https://1000randomnames.com/). I would like to combine them with hiding the formula in the column heading.
First Attempt:
A few notes:
- {}s
- starts and ends with the curly brackets to indicate it contains information for multiple cells
- arrayformula
- allows the user to apply the formula to multiple rows
- &
- concatenate doesn't work with arrayformula so this joins the data
Now this creates a huge problem, once we scroll down past the actual data, we can see what happened:
This formula doesn't know when to stop. If the user was to print this, it'll print multiple pages of just a comma in the third column. We'll also ignore the cruel randomness of giving Ophelia flowers. "Nobody out crazies Ophelia" - Lisa Simpson.
Second Attempt:
A few more notes:
- if(isblank(A2:A), true, false)
- this performs a check to see if the cell in the first column of the row is blank, if it is, leave the combined name blank if not, perform the join
- this gets rid of the extra ","s at the end of the data
- watch for an error inducing extra ")" at the end of the formula when you type it in, I always forget to end the bracket for the arrayformula and Google Sheets "helpfully" puts in an end bracket
Hopefully, this helps with people deleting your formulas. If they add data into column C, everything disappears and the heading changes to an error. That is sure to get their attention.
Comments ()