Any ideas on constructing more user-proof formulae?
I'm having trouble with users inserting rows in a sheet versus appending new rows at the bottom. Locking rows doesn't prevent an insert (would be nice if a non-admin user couldn't insert when adjacent rows are locked).
I've used the following formula to ensure the first row of a Sheet always contains a copy of the data in the last completed row of the Sheet (which always has the latest report date):
=INDEX($Project$4:$[Activities Planned Next Period]$30, MATCH(MAX([Report Period End]:[Report Period End]), $[Report Period End]$4:$[Report Period End]$30, 1), 1)
The INDEX and MATCH formula point to the correct cell driven by the result of the MAX formula which finds the newest date.
Typical errors are the user inserts a row and makes the formula unparseable, or the maximum row number of the sheet is changed and breaks the formula.
Any good ideas and practices to avoid this kind of problem? I know that I can drive data appending from a web form but in this application I need the user going direct to the form.
BTW I use Row 1 of the sheet to drive a linked Sight dashboard. The link from the Sight is 'absoulte' so needs the freshest data to alwasy be in one position.
thanks
Mark