Users!!

[TinaP]

I just spent three hours trying to figure out why two worksheets didn't balance. Turns out, the user had over-written a complex sumproduct formula (aren't they all) and it was still using the data from last month.

Anybody know a way to slap the fingers of users who don't respect formulas?

 

[shg]

TP their monitor.

I have a simple macro that creates a map of a worksheet. It makes clear where hard-coded inputs are placed among formulas. Happy to post it.

 

[Smitty]

Anybody know a way to slap the fingers of users who don't respect formulas?

In an ideal world we'd just revoke their life privileges.

 

[MARK858]

I thought that was what this thread was for...
http://www.mrexcel.com/forum/lounge-v-2-0/27058-good-excel-practical-jokes-pranks-mean-tricks-etc.html

or personally I favour bringing back the Stocks and the Rack for the person at work on Tuesday who sent me a workbook where they said the formulas had stopped working after they had put in 16 merged cells

 

[SydneyGeek]

Any chance you could lock down the sheet, leaving data entry cells accessible?
Whether that's feasible depends on whether there will be filtering, sorting or other manipulations.

I've used colour-tagging previously to find those sorts of issues, but stopping users from trashing workbooks is an uphill battle.

Other options include loading from a database to a Data sheet, and having formulas populate the worksheets from there. Each month's updates are then loaded to the DB, ready for the next round.

Denis

 

[Jon von der Heyden]

Worksheet protection - it's what it's for!

If calculations do not need to be part of an input sheet then keep it away from the inputs.

Use styles - with a dedicated style just for inputs. Make sure that the cell "locked" property for that style is set to False and that other styles are set to True.

 

[TinaP]

I usually protect worksheets with formulas, but I can't do that with this worksheet due to its function. I modified John Walkenbach's worksheet mapping macro and now I have a tool to sort it all out. When I ran it on the worksheet in question, it found other areas with overwritten formulas.

 

[arkusM]

TP their monitor.

I have a simple macro that creates a map of a worksheet. It makes clear where hard-coded inputs are placed among formulas. Happy to post it.

I would be interested in this code, if you don't mind. Cheers.
Mark

 

[arkusM]

I just spent three hours trying to figure out why two worksheets didn't balance. Turns out, the user had over-written a complex sumproduct formula (aren't they all) and it was still using the data from last month.

Anybody know a way to slap the fingers of users who don't respect formulas?

I had a similair experiance on Monday, "spreadsheet is not working".... ME: "uhm you deleted the SUM formula........" LOL.
One time I put a tracker on all changes a user did, but that was not as usefully as I hoped.. I wish the Cell Validation was a bit more flexible.
Could you use a Worksheet change event? I have used that as well to prevent Excel from converting email addresses into links.

 

[shg]

Sure; it's posted at https://app.box.com/s/z6qv4zbz8xy17d5ziesn.

From the header:

' ============================== S H E E T   M A P =============================

' Creates a sheet map to characterize the contents of each cell with a color,
' and, for non-empty cells, a two-character code

'   Color:
'       Dark Grey   Empty
'       Light Grey  Formula
'       Yellow      A number or date stored as text
'       Red         An error
'       White       None of the above

'   First character => formula or literal:
'       L   A literal
'       F   A formula
'       <   A formula the same as that at left
'       ^   A formula the same as that above
'       +   A formula the same as those above and left

'   Second character => type of value:
'       $   String      ' from the type declaration character
'       @   Currency    ' from the type declaration character
'       #   Double      ' from the type declaration character
'       D   Date
'       E   Error