# Users!!



## TinaP (Jul 12, 2013)

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 (Jul 12, 2013)

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 (Jul 12, 2013)

TinaP said:


> 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 (Jul 12, 2013)

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 (Jul 15, 2013)

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 (Jul 16, 2013)

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 (Jul 16, 2013)

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 (Jul 16, 2013)

shg said:


> 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 (Jul 16, 2013)

TinaP said:


> 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 (Jul 16, 2013)

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
```


----------



## arkusM (Jul 16, 2013)

shg said:


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



Thanks, I always apprecaite an oppotunity to try something new!! Cheers!!

Mark


----------



## Jon von der Heyden (Jul 16, 2013)

shg said:


> Sure; it's posted at https://app.box.com/s/z6qv4zbz8xy17d5ziesn.
> 
> From the header:
> 
> ...



I've just had a look.  Thanks for sharing - this is a little gem.  I especially like the formula <^+ test!  I'm definitely going to use this.  I use styles to indicate inputs from other cells, and I use DV a lot - so I'm going to tailor this a bit to indicate cells with DV and where the inputs are.  Thanks again for sharing!


----------



## shg (Jul 16, 2013)

You're welcome, glad you like it. The idea was not original; over the years I saw several requests for a link to something that apparently did something similar. I never found it, but coded it from their various arm-waving descriptions. As you can see, it's not complicated.

My youngest is a business analyst for a medical lab company -- she uses it to see at a glance where people have hard-coded numbers that should be formulas


----------



## Felix Atagong (Oct 7, 2013)

Sorry for reviving this old thing, but today I had the bookkeeping shouting at me 'your f###ing Excel doesn't work'.
They have this financial overview page that links to other pages and of course it was my fault that the numbers on the overview page weren't the same as on the original pages.

I looked for about a minute at the formula and then I said, obviously, your formula is pointing to a page called 
"september2013.xls" but the page containing the original numbers is called 
"september 2013.xls" and on top of that the tab is named 'sheet1' while the formula points to a tab with the cryptic name '6'.

So that matters then?
Yes, it matters.


----------



## Felix Atagong (Oct 23, 2013)

Phonecall from sales department 5 minutes ago.

"Felix, the bookkeeping department just told me I can't send offers in Excel any more, they have to be in pdf for legal reasons. How do I do that?"
"In Excel, just choose Save As and save as a pdf file."
"Isn't there an easier way?"


----------



## Jon von der Heyden (Oct 23, 2013)

Felix Atagong said:


> "Isn't there an easier way?"


Yes there is! Employ an admin with a brain and have him/her do it for you!


----------



## arkusM (Oct 23, 2013)

Felix Atagong said:


> Phonecall from sales department 5 minutes ago.
> 
> "Felix, the bookkeeping department just told me I can't send offers in Excel any more, they have to be in pdf for legal reasons. How do I do that?"
> "In Excel, just choose Save As and save as a pdf file."
> "Isn't there an easier way?"



Facepalm.


----------



## Joe4 (Oct 23, 2013)

> "Felix, the bookkeeping department just told me I can't send offers in Excel any more, they have to be in pdf for legal reasons. How do I do that?"
> "In Excel, just choose Save As and save as a pdf file."
> "Isn't there an easier way?"


We have a few users like that too.
Its times like that where I would love to say: "*Find a 5 year old to do it for you.  I am pretty sure they can handle it.*".
But alas, I like staying employed...


----------



## arkusM (Oct 23, 2013)

Felix Atagong said:


> Phonecall from sales department 5 minutes ago.
> 
> "Felix, the bookkeeping department just told me I can't send offers in Excel any more, they have to be in pdf for legal reasons. How do I do that?"
> "In Excel, just choose Save As and save as a pdf file."
> "Isn't there an easier way?"



I have long thought it would be nice to program a "god" program that does everything I need it to do without my intervention... 
I am not up to the task and I do need to eat and have a warm place to stay. 
Besides it is likely DARPA or some other group of "s,m,r,t" people are working on our robotic overloads already.


----------



## Jon von der Heyden (Oct 23, 2013)

I think people that can't handle simple computer tasks should go back to using ledger books and an abacus.


----------



## Smitty (Oct 23, 2013)

arkusM said:


> Besides it is likely DARPA or some other group of "s,m,r,t" people are working on our robotic overloads already.



Hmmm, kind of like the government idiots behind healthcare.gov?


----------



## arkusM (Oct 30, 2013)

Smitty said:


> Hmmm, kind of like the government idiots behind healthcare.gov?


Bwahahaha....


----------

