Constructing User-proof formula

Mark Johnson1

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

J. Craig Williams

Mark,

 

Can you share a screen shot to clarify?

Is [Report Period End] user entered or one of the System columns?

 

Craig

Mark Johnson1

 

Does this help Craig ?  Formula above sits in each cell of Row 1.

 

thanks

 

Mark

 

J. Craig Williams

Mark,

 

Is row 30 the last populated row or is there blank rows above it?

When user insert rows, are they inserting at the bottom or  in the middle?

Why start at row 4 and not row 3? Depending on the answer, row 2 may be better as it won't change if someone adds a new row 3 or 4.

 

Craig

Mark Johnson1

The screen shot is hard to read on the forum.  Easier if you open it.  For precision, the two date columns simply use the formula: '=MAX([Report Period End]:[Report Period End])'.  The other columns use the formula above.

 

Mark

Mark Johnson1

Re row 30: arbitrary row choice sufficient to handle 30 weeks of updates.  so blank rows above it.

 

Re User insert rows: user is supposed to just fill in the next empty row, but they often forget the instruction and insert at 2, 3, 4 or whatever!

J. Craig Williams

Mark,

 

Thanks for the information.

 

Here are my suggestions:

 

1. Change your reference from row $4 to row $2 (your commented row).

If when someone adds a row below $2, the reference will stay $2

2. Add a comment in row 2 that new rows get added below.

3. If someone adds above row 2, slap them. Or at least give them a dirty look.

 

Now the other common problems are

1. adding below row 30 (which is 28 data rows) 

2. date that end the same period.

 

Either there is one new row per week and (2) does not apply, or this is a set of projects that could have some end on the same day. I assume the first. Let me know if I am wrong.

 

For (1), I assume you clear out the data periodically so this is also not a concern.

If it is, let me know.

 

To add new rows, you might also consider using a WebForm. That will go exactly where you want it to.

 

Hope this helps.

 

Craig

 

 

 

 

 

 

 

J. Craig Williams

Glad I could (help)

 

Craig

Mark Johnson1

Craig - thanks.  Agreed re change to $2 ref.  Certainly helps. 

 

Thankfully in this instance it's a single project being referenced and each row is a brief set of highlight report snipppets that are pulled together in a Sight.  So no two rows have the same end date.  The formula just takes the first row if it happens inadvertently. 

 

30 rows is usually enough and earlier data can be cleared/archived as you suggest.  I settled on 30 as I ran into trouble with the formula not liking the number of rows being expanded in the Sheet.

 

Web form won't cut it in this instance as it's just not the right approach for the users.  They need to cut & paste from previous rows etc.

 

In the perfect world the logic wouldn't be in the Sheet, or at least better protected if it is.  Hence the more general question of hints & tips for user-proofing :- )

 

Appreciate the help.

 

Mark