Users!!

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
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
 
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.
 
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.
 
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
 
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.
 
Sure; it's posted at https://app.box.com/s/z6qv4zbz8xy17d5ziesn.

From the header:

Code:
' ============================== 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
 

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top