# So Many Spreadsheets



## MorganO (Mar 18, 2009)

Originally posted at TheDailyWTF.com but I thought the crowd here would enjoy.

----------------------
*So Many Spreadsheets* (submitted anonymously)
A few years ago, I had the misfortune to train a really nice, but really dim employee on our accounting system. I sat with her for several days, showing her the ins and outs of the admittedly complex system, but she could never quite figure things out on her own. I recommended that we have someone else do what she was supposed to do, but management insisted that she do those things.
Fast forward a year and several big screw-ups later, and management decided that she was better off as the receptionist, after all. Fortunately, we were able to get a really competent gal to take over her tasks.
When I was chatting with the new girl, she told me about all these spreadsheets that she had been sorting through from the previous employee. Dozens of them named things like Customers1.xls, Customers2.xls, and Customers3.xls. She said that she stared at these things for the longest time, trying to figure out why they were in separate documents, since they seemed to be continuations of the same data.
And then it dawned on her. Each of the documents had about 23 rows of data. 23 rows is what was fitting on a screenful in Excel on that computer. The previous employee didn't know you could scroll down.


----------



## NateO (Mar 18, 2009)

MorganO said:


> Each of the documents had about 23 rows of data. 23 rows is what was fitting on a screenful in Excel on that computer. The previous employee didn't know you could scroll down.


 
Whoa! 

Should have got her a bigger monitor!


----------



## NateO (Mar 18, 2009)

After pondering this story, I'm finding it a little hard to believe at face value - who doesn't know how to scroll in virtually any application?

I submitted a story for the the site in question, and the author embellished a little.


----------



## lenze (Mar 18, 2009)

> I'm finding it a little hard to believe at face value - who doesn't know how to scroll in virtually any application?


Nate, you've obviously never worked with "Instructionally Challenged Political Appointees"

lenze


----------



## Cbrine (Mar 19, 2009)

Lenze is correct.  I once had an HR Manager ask me for some excel help.  I went over to her computer and listened to what she wanted, then entered her formula for her...

=B2+C2


----------



## Domski (Mar 19, 2009)

Cbrine said:


> Lenze is correct. I once had an HR Manager ask me for some excel help. I went over to her computer and listened to what she wanted, then entered her formula for her...
> 
> =B2+C2


 
First time I looked at a spreadsheet I'd absolutely no idea what it was and couldn't even have done that. Some would say not much has changed.


----------



## texasalynn (Mar 19, 2009)

Definitely some are out there - I had someone ask me how for format a cell as $xxx's.  Then when I taught classes they wanted to sign up for the intermediate class.


----------



## SydneyGeek (Mar 19, 2009)

With training, people assume that if they've used Excel for a couple (maybe up to 6!!) years they must be ready for Advanced. Never mind that it's been a glorified list manager and they have never entered a formula in their life... 
I still come across people who should know better, but just type over formulas and convert them to values. Then you get the call that the spreadsheet isn't working 

Denis


----------



## Smitty (Mar 19, 2009)

I think I brought this one up recently, but before I left the PennySaver I created a consistent, company-wide expense report. Everyone signed off on it, but the day before distribution I get a call from the head of Accounts Payable that she can't send it out! She can't change the date and gets a warning that the cell's protected! She absolutely can't send it out this way and I have to fix it right now!

I calmly explain that when she opens it tomorrow the wb will show tomorrow's date.  I then explain that it's a simple formula (TODAY).

She says "It will?!"

Yup...

"Wow, you're going to have to come down here and show us how to do some of this stuff!"

She's been using Excel for over 10 years...Go figger...


----------



## gingerafro (Mar 20, 2009)

I've got a finance department that regularly uses a calculator and enters the result into the spreadsheet.


----------



## yytsunamiyy (Mar 20, 2009)

gingerafro said:


> I've got a finance department that regularly uses a calculator and enters the result into the spreadsheet.



Seen THAT quite a few times. If you want some woreshipping offer them to automate some of their tasks - then enter - where they can't see you - such complicated formulas as the one mentioned by Cbrine in the appropriate cells and return the wb to them - the look on their faces when everyhing updates automatically is reward enough...

...until they start checking the results using their beloved calculator...


----------



## arkusM (Mar 20, 2009)

gingerafro said:


> I've got a finance department that regularly uses a calculator and enters the result into the spreadsheet.


 
So I'm not the only one who has witnessed this!! 



> ...until they start checking the results using their beloved calculator...


 
Ha this just happened to me. I did a sheet for our lunch time poker games!!  I did a simple difference between point column, so #1 position is ahead of #2 by x points. One of our geophysicists couldn't figure out what the column meant. He did the math himself and the numbers did not match, they were out by 0.001……. And the computer surely couldn’t do the math wrong… 

I was so surprised that I did not even get to explain the sheet was set to display only two decimal places…

Good times, good times!!


----------



## btadams (Mar 20, 2009)

In a previous life I used to teach Excel courses for the employees of the company I worked for. One day I was passing by the cube of one of my co-workers (who had attended my training). I watched as she was putting "borders" around a bunch of cells using the line-drawing tool. She must have spent hours doing this. Then she did an autofit to her columns. The lines she drew no longer bordered the cells. She did an almost exact imitation of John Belushi in Animal House when the guy dropped the box of whiskey.


----------



## VoG (Mar 21, 2009)

Mrs VoG works freelance for a number of promotions/merchandising companies. She's just done some work for a new company and was e-mailed a spreadsheet to complete for her hours worked. This contains minimal formatting, no protection, no validation and no formulas. So, for example, she has to enter hours worked under Mon, Tue, Wed ... and then manually enter the total hours in a Total hours cell.

But it gets worse. Does she now save and e-mail the spreadsheet back? No - she has to print it out and post it. And, presumably, some poor sod at the other end then has to enter her data in another spreadsheet. The mind boggles


----------



## NateO (Mar 21, 2009)

gingerafro said:


> I've got a finance department that regularly uses a calculator and enters the result into the spreadsheet.


 
I can't even believe how many people think this is a great idea... Why do you want a calculator when you're using Excel?


----------



## bam12 (Mar 21, 2009)

Sounds like we all work with those kinds

reminds me of the movie Butch Cassidy and Sundance Kid, Butch and Sundance wondering where they are going to be ambushed while going to the bank to pick up the payroll.  LOL


----------



## ZVI (Mar 21, 2009)

NateO said:


> I can't even believe how many people think this is a great idea... Why do you want a calculator when you're using Excel?


Someone has explained me the reason of the usage of calculator by bosses and top managers. 
Some of them don't prefer or even can’t use the keyboard!
Seems that the virtual keyboard will be more suitable in this case 

```
<font face=Courier New>
Sub Auto_Open()
  Shell "osk.exe"
End Sub</FONT>
```


----------



## VoG (Mar 21, 2009)

ZVI said:


> Someone has explained me the reason of the usage of calculator by bosses and top managers.
> Some of them don't prefer or even can’t use the keyboard!
> Seems that the virtual keyboard will be more suitable in this case
> 
> ...



Even on this board, Vladimir http://www.mrexcel.com/forum/showthread.php?t=372903


----------



## ZVI (Mar 22, 2009)

VoG said:


> Even on this board, Vladimir http://www.mrexcel.com/forum/showthread.php?t=372903


Oh, I see - it should be happened here… 

Peter, I could post a code for pop-up of MS Calc by the double clicking on numerical cells of Excel for editing its value in Calc. Surely, it is the tool for Big Bosses only 
But where is the proper place for that, here or may be on the thread you've underlined?


----------



## VoG (Mar 22, 2009)

ZVI said:


> Oh, I see - it should be happened here…
> 
> Peter, I could post a code for pop-up of MS Calc by the double clicking on numerical cells of Excel for editing its value in Calc. Surely, it is the tool for Big Bosses only
> But where is the proper place for that, here or may be on the thread you've underlined?



I think here - that was a rather old thread.


----------



## ZVI (Mar 22, 2009)

There is the code for pop-up MS Calc by double clicking on Excel cell with numeric constant value. Other type of cell (date/formula/empty etc) not triggers the Calc. Editing cell is marked by red double lines borders.

The value of the double clicked cell is auto copied into edit textbox of MS Calc and can be used as initial value of calculator. If Calc window closes or loses the focus then MsgBox is appeared with question of applying changed value or not.

This is code for standard VBA-module:

```
<font face=Courier New>
' ZVI:2009-03-22 Pop-up of Windows Calculator by double clicking.
' All code below should be copied into standard VBA module.
Option Explicit

Type POINTAPI
  x As Long
  y As Long
End Type

Type RECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hWnd As Long, lpRect As RECT) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String) As Long
Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

Const SM_CXFULLSCREEN = 16
Const SM_CYFULLSCREEN = 17
Const WM_CL0SE = &H10
Const WM_SETTEXT = &HC
Const WM_GETTEXT = &HD
Const WM_GETTEXTLENGTH = &HE
Const HWND_TOPMOST = -1
Const SWP_NOSIZE = &H1

Dim TimerID&, CalcHwnd&, CalcCaption$, EditHwnd&, EditText$
Dim CalcCell As Range, CalcRect As RECT, bls, bci

Sub RunCalc()
  Dim s$
  On Error Resume Next
  s = Str(CDec(ActiveCell.Value) + 0)
  If Err <> 0 Then Exit Sub
  s = s & "="
  StopCalc
  Set CalcCell = ActiveCell
  CalcCaption = CalcCell.Parent.Name & "!" & CalcCell.Address(0, 0)
  Shell "calc", vbNormalFocus
  If Err <> 0 Then MsgBox "Calc.exe not found", vbCritical, "Error": Exit Sub
  CalcHwnd = GetForegroundWindow
  SetPosition
  SetWindowText CalcHwnd, CalcCaption
  EditHwnd = FindWindowEx(CalcHwnd, 0, "Edit", vbNullString)
  SetDoubleBorders
  Application.SendKeys s
  TimerID = SetTimer(0&, 0&, 100&, AddressOf MyTimer)
End Sub

Sub StopCalc()
  Dim v#
  On Error Resume Next
  KillTimer 0&, TimerID: TimerID = 0&
  If CalcHwnd <> 0 Then
    PostMessage CalcHwnd, WM_CL0SE, 0&, 0&
    CalcHwnd = 0
    RestoreBorders
    SaveSetting "ZVI", "Calc", "X", CalcRect.Left
    SaveSetting "ZVI", "Calc", "Y", CalcRect.Top
  End If
  If Len(EditText) = 0 Then Exit Sub
  v = EditText
  If Err <> 0 Then v = Val("&H" & EditText)
  If CStr(v) <> CStr(CalcCell.Value) Then
    If MsgBox("Change the value of " & CalcCaption & " ?" & vbLf _
              & "Old:" & vbTab & CalcCell & vbLf _
              & "New:" & vbTab & v, vbYesNo, CalcCaption) = vbYes _
    Then
      CalcCell.Value = v
    End If
  End If
  Set CalcCell = Nothing
  EditText = ""
End Sub

Private Sub MyTimer(ByVal hWnd&, ByVal uMsg&, ByVal nIDEvent&, ByVal dwTimer&)
  CheckCalc
End Sub

Private Sub CheckCalc()
  On Error Resume Next
  If GetForegroundWindow <> CalcHwnd Then
    If EditText = "" Then EditText = "0"
    StopCalc
  Else
    EditText = Space(SendMessage(EditHwnd, WM_GETTEXTLENGTH, 0&, 0&))
    SendMessage EditHwnd, WM_GETTEXT, Len(EditText) + 1, ByVal EditText
    GetWindowRect CalcHwnd, CalcRect
  End If
End Sub

Private Sub SetPosition()
  Dim pt As POINTAPI
  GetWindowRect CalcHwnd, CalcRect
  pt.x = (GetSystemMetrics(SM_CXFULLSCREEN) - (CalcRect.Right - CalcRect.Left)) / 2
  pt.y = (GetSystemMetrics(SM_CYFULLSCREEN) - (CalcRect.Bottom - CalcRect.Top)) / 2
  pt.x = GetSetting("ZVI", "Calc", "X", Str(pt.x))
  pt.y = GetSetting("ZVI", "Calc", "Y", Str(pt.y))
  SetWindowPos CalcHwnd, HWND_TOPMOST, pt.x, pt.y, 0&, 0&, SWP_NOSIZE
End Sub

Private Sub SetDoubleBorders()
  On Error Resume Next
  With CalcCell.Borders
    bls = .LineStyle
    bci = .ColorIndex
    .LineStyle = xlDouble
    .ColorIndex = 3
  End With
End Sub

Private Sub RestoreBorders()
  On Error Resume Next
  With CalcCell.Borders
    .LineStyle = bls
    .ColorIndex = bci
  End With
End Sub</FONT>
```

The code of ThisWorbook (class) module for double click triggering:

```
<font face=Courier New>
' Code of ThisWorbook VBA (class) module
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Dim i&
  On Error Resume Next
  If Target.HasFormula Then Exit Sub
  i = VarType(Target.Value)
  If (i < vbInteger Or i > vbCurrency) And i <> vbDecimal Then Exit Sub
  Cancel = True
  RunCalc
End Sub</FONT>
```

Make the Boss happy, 
Vladimir


----------



## ZVI (Mar 22, 2009)

For working in all workbooks the code of ThisWorkbook module should be like this:

```
<font face=Courier New>
' XLA solution
' Code of ThisWorbook VBA (class) module for working with all workbooks
Public WithEvents App As Application

Private Sub App_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Dim i&
  On Error Resume Next
  If Target.HasFormula Then Exit Sub
  i = VarType(Target.Value)
  If (i < vbInteger Or i > vbCurrency) And i <> vbDecimal Then Exit Sub
  Cancel = True
  RunCalc
End Sub

Private Sub Workbook_Open()
  Set App = Application
End Sub</FONT>
```

The workbook with such code can be saved as XLA for using as AddIn


----------



## Long Nose (Mar 23, 2009)

I see coworkers spending most of their day banging out numbers on a calculator and running back and forth to our one centrally located printer.  Yes, they have excel. 

I must look like a total slacker because I rarely print anything.  Perhaps I should start printing something?


----------



## NateO (Mar 23, 2009)

Long Nose said:


> I must look like a total slacker because I rarely print anything. Perhaps I should start printing something?


Save the planet - go paperless!


----------



## texasalynn (Mar 23, 2009)

Long Nose said:


> I see coworkers spending most of their day banging out numbers on a calculator and running back and forth to our one centrally located printer.  Yes, they have excel.
> 
> I must look like a total slacker because I rarely print anything.  Perhaps I should start printing something?



Or do like most users and print before checking the layout and end up printing pages with a single column, or many blank pages.


----------



## NateO (Mar 23, 2009)

texasalynn said:


> Or do like most users and print before checking the layout and end up printing pages with a single column, or many blank pages.


 
I do that from time to time...


----------



## schielrn (Mar 23, 2009)

NateO said:


> Save the planet - go paperless!


Don't listen to this, you should print hard copies of everything    Its not like I work for a paper company or anything.


----------



## Long Nose (Mar 23, 2009)

schielrn said:


> Don't listen to this, you should print hard copies of everything    Its not like I work for a paper company or anything.



You don't also run a turnip farm with your brother, do you?


----------



## SydneyGeek (Mar 23, 2009)

I don't print much. Maybe a ream every 6 months -- pretty well everything else is electronic (but in multiple backups). The downside is, when the desks get reorganised I'm the one who moves because it's easy to do...

Denis


----------



## Andrew Fergus (Mar 23, 2009)

Oh dear - the other day I was asked to 'corporatise' a graph.  I took this to mean that I need to make it prettier and in the process obfuscate the meaning and intention of the graph.  Why or why are people stuck on the appearance of something and not the content?????


----------



## lenze (Mar 23, 2009)

> Why or why are people stuck on the appearance of something and not the content?????


Yeah, I know what you mean. When I worked for the state I would help others with SS, but only if they had 4 or less colors. 5+ gives me a headache!!
lenze


----------



## SydneyGeek (Mar 23, 2009)

lenze said:


> Yeah, I know what you mean. When I worked for the state I would help others with SS, but only if they had 4 or less colors. 5+ gives me a headache!!
> lenze


 
I agree -- there's a fine line between information and glitz. Good presentation will always require some layout skill but using some default colour / font combination because Marketing dreamt it up, is often a great way to hide the content.

Denis


----------



## ZVI (Mar 23, 2009)

On the other hand, who prints documents too seldom or one too creative can use, for example, dark green background cells with white font.
They are not conceived about amount of toner required for printout and an extra energy for chemical manufacturing.
Hereunder it means thoughtless spending of planet resources and encourages worldwide warming of climate. 
So, don't print or don't use filling cells - save the planet!


----------



## Long Nose (Mar 24, 2009)

Chemical manufacturers are people too.  They might take a different view of this reduced demand.


----------



## arkusM (Mar 24, 2009)

Andrew Fergus said:


> Why or why are people stuck on the appearance of something and not the content?????


 
Haha!! Rhetorical question right? Marketers around the world have relied on peoples desire for enhanced appearances. To my mind this human desire is what has driven the world to the brink of economic disaster.

I understand your frustration though.


----------



## sous2817 (Mar 24, 2009)

Ha!  I spent 5 hours of the phone yesterday with people in 3 different countries doing just that.  After they deemed it sufficiently pretty, it was sent to the project manager and told "this is trash".  They wanted me to explain to upper management how long it took...I was utterly embarrassed.  “But…but..but, it took 5 hours!” they complained.  Apparently they want to measure performance by the amount of time put in, not the quality put out.

I wonder if I’d get my hand slapped for making your statement my corporate email signature…




Andrew Fergus said:


> Oh dear - the other day I was asked to 'corporatise' a graph.  I took this to mean that I need to make it prettier and in the process obfuscate the meaning and intention of the graph.  Why or why are people stuck on the appearance of something and not the content?????


----------



## Greg Truby (Mar 24, 2009)

Andrew Fergus said:


> Why or why are people stuck on the appearance of something and not the content?


Andrew, my friend, that has to be the single dumbest question I have ever seen posted on this board. Do you get no U.S. news down there? How the he11 are them smartypants fellers in NYC s'pposed ta get them $165 million bonuses while utterly destroying their company if'n they use charts with real content?!


----------



## Andrew Fergus (Mar 24, 2009)

I know I know.......it drives me nuts and is symptomatic of the current financial crisis we all find ourselves in.  Unfortunately I feel like I am part of the problem and not part of the solution........hmmmmmm.

In addition to 'corporatising' graphs, we now have to 'monetarise' things too!  It is so tempting to say "Don't verb nouns!" but I'm guessing that would be a real conversation stopper........

Cheers
Andrew


----------

