Monthly Archives: June 2008

Office Mobile

Posted on by
8

With my new Windows Mobile phone came Office Mobile, including Excel Mobile version 6.1.  (I always thought it was Pocket Office and Pocket Excel, but my phone says otherwise.)

Being an Excel guy, I was kind of excited to see this because... er... it's Excel.  What other reason do you really need?  :)  Now, to set expectations, I was aware that there was no VBA in it, and I'm okay with that.  I just really wanted to see how much they could pack into it.

So off I go into Excel Mobile, and it asks me what file I want to open.  Hmm... I don't have any Excel files on my phone, so I'll just create a new one... huh?

IF there is a way to create a new Excel document on the Windows Mobile 6 smartphone, it is so well hidden that there might as well not be.  No amount of clicking on the simple menus would yield it for me, and there are no template installed by default.  Not so good, I'm thinking.  At any rate, I downloaded one from my email, opened it, cleared all the cells and saved a copy as a blank workbook on my phone.  So at least now I have a way to start from a fresh file.  It even gave me a warning that I might lose features if I saved it in that format.
Now, when it first opens the file, it goes into full screen mode.  At first I thought it was just a viewer, but after a bunch of key pressing, I finally figured out that if you click the right "soft key" up on the phone, it will actually give you a menu.  The UI is pretty simple, so I've listed it all below. (Simon Murphy might like it... it doesn't have a Ribbon.  😉 )
The initial Menu in the viewer screen includes:

  • Close File
  • Edit
  • File (with Save As and Send... as options)
  • Go To
  • Find
  • About

So naturally, I'm heading into Edit mode.  In here, I have two menus.  The left soft key gives me a View menu including:

  • Full Screen
  • Cell Text
  • Overview
  • Freeze Panes
  • Zoom (with a 5 presets from 50% to 150% in 15% increments)
  • Sheet (with each sheet listed)

The right soft key gives me a "Menu" menu with the following:

  • Close File
  • Preview
  • File (Save As & Send...)
  • Edit (Cell Text, Clear Cell, Define Name, Delete Cells, Cut, Copy, Paste)
  • Insert (Row, Column, Shift Right, Shift Down)
  • Select (Cells, Columns, Rows, All)
  • Tools (Sort, Find, Go To)
  • Undo
  • Redo

And that's it for menus, folks.  What surprised me here was that Define Name was part of the selection.  The rest all make sense to me, but I see Define Name as more of a developer command than a user command. I guess it is logical though, as it could be used with Go To since the device has such a small screen.

I'll admit that I found the inital View screen weird, as it was full screen, and none of the formulas showed, but rather only the results.  Once you get in to edit the sheets, though, you can see the formulas.

Creating content is clunky, but then it isn't really surprising given the device, I guess.  To enter anything in a cell, you need to click the centre navigation button to get into Edit mode, then you have to manually type (on the tiny keyboard) your entire formula or data.  There is no arrowing over one cell in a formula... you have to write in the whole thing one keystroke at a time.  Maybe it will get easier in time as I get better with the keyboard, I don't know.

At any rate, it's not set up for full development, but then we knew that.  I was able to set up a pretty basic spreadsheet to track how many kilometers I get vs how many litres of fuel I used since the last fill for my car though.  So it does work.

I'd have to say that I wasn't expecting much, truly, so am pretty happy with what is there.  The lack of ability to create a new file is a pretty big oversight though.  Hopefully they'll fix that at some point.
I just think it's pretty cool to tell people I have Excel on my phone.  They usually nod and smile and say "Of course you do, Ken".  🙂

Windows Mobile Smartphone

Posted on by
Reply

As of this afternoon, we've finally got our new Windows Mobile Smartphones hooked up to our Exchange server.  🙂

This is really cool, as I no longer have to plug it in to sync up the device with my Outlook installation.  I've got full access to my task list, contacts and calendar, which are updated on virtually a live basis.  (I actually created a new appointment in Outlook, picked up my phone, and it was already there.  Very nice!)

In addition, of course, I can access my work email from anywhere.  It's funny to me that this seems to be the number one selling point on these devices, and yet it's the last piece I was really interested in.  I try to leave my email at work, as I have my own email to deal with in off hours.  Regardless, it is nice that we didn't have to set it up where every email is pushed down to us as it comes in, but rather we can use "Pocket Outlook" to retrieve the email when we want to read it.  It's not quite POP3 email, though, as it does retrieve it to the phone regularly without me asking, but I don't get notified every time an email comes in.

The biggest win for me here so far is the task list and calendar being online.  Earlier this week, (while I was syncing manually,) I went to the dentist.  When it came time to set the next appointment, I could check my schedule 3 months out and see where I had an opening.  No more guessing!  And then I could add it right to the device and I'm done.  Gotta like that.  And then the tasks... I was out at dinner with my wife, and something she said reminded me of something I had to do at work... that wasn't on my list.  A minute later, it was linked into my task list, and I'm good to go.   This was the whole reason for I wanted one of these babies.  The great thing now is that as soon as I enter it, it's on the server and good to go.  So if I log in via my web portal, it's there.  If I'm on the Smartphone, it's there. 😉
I'm pretty stoked about this whole thing, and really impressed with the phone so far.  The carrier though... not so much...

I used my IT Contractor to set up the Exchange server intergration for the Smartphone.  (Word to those looking at Blackberry and shuddering over the cost of the Blackberry server... if you have an exchange server, you can set up Windows Mobile devices already, and the functionality is better!)  At any rate, we're working on the install, and I run into an issue with my digital certificate.  It's trusted under the private key for my mail server, but the Root CA for Verisign isn't installed on the SmartPhone.  Without that, the private (leaf) certificate can't be trusted, as it was issued by Verisign, and Verisign isn't a trusted authority.  (Crazy, as Verisign is only one of the biggest CA vendors, so why Telus doesn't have it installed by default is beyond me...)

My IT Contractor suggests that Telus's Tech Support department should be able to tell me how to get the Verisign Root CA onto my phone, and asks me to call Telus... which turned out to be a waste of time.  Here's my route through:

  • Call the Tech Support number in the box --> Get told that I'm a corporate customer (duh) and transferred to Corporate (General)
  • Corporate General is confused by my question and figures out that I need Tech Support --> They transfer me to the Corporate Tech Support department... in French... with no option to bail to an English menu
  • I hang up and call back
  • I request Corporate Tech Support.  They can't do that, so transfer me back to Corporate General again
  • I request Tech Support.  The CSR asks what the issue is, so she can tell Corporate Tech Support, then admits she doesn't understand a word of what I tell her
  • I finally get Corporate Tech Support (in English) and the guy is USELESS
  • After about 10 minutes of my telling him that I'm trying to sync wirelessly and reinstalling software on my PC isn't going to make that happen, he refers me to... get this... Microsoft!  I kid you not, he actually gave me Microsoft's phone number.

The short synopsis of the call to Telus is that I basically got told by they don't support the setup of the device, they just sell them.  Nice!  How these guys end up with every increasing stock prices seriously baffles me, as everyone I know despises the company, from the employees to the customers.
I get so frustrated with Tech Support departments where the tech has no clue how to use their own hardware/software.  (I recently had an issue with Symantec where, after 10 minutes of using their product, I was teaching their Tech Support guys how to use it!)  Why can't companies actually train their staff properly to support what they sell?  All this guy really needed to do was identify what the issue was... he didn't even know what a digital certificate was!  They sell these to Corporate users (hence the department name), so surely I can't be the only Exchange shop who has run into this issue?
At any rate, I was Googling the issue the whole time I was arguing with the "Tech Support" guy, and found this awesome tool which fixed my issue for me.  It basically extracts all the required components of the digital certificates that need to be installed on the client to allow the Mobile Device to link in to the server.

I should also say that my IT Contractor, after doing some research, has let me know that my Verisign cert wasn't actually installed properly, so I shouldn't have had to go to this length.  They have apparently fixed it now.  🙂

Now, for the funny part... Because I'd been syncing my contacts and calendar items via USB, it duplicated all of them when I switched it to the Exchange server!  I made an attempt at coding a duplicate removal in Outlook, but gave up on it.  (Ran fine on my local (non-Exchange) Outlook in tests, but came up short when I tried to run it live on my work install.)  So I had to manually edit my contacts and calendar items, but I'm all good now.

I love technology... when it works.  When it doesn't it can sure be frustrating though, but it make it all worthwhile when you finally get it right and it pays immediate dividends.

Marking cells without dependents

Posted on by
2

I tripped on this in a forum today...

The user wanted to create a message for all cells that did not have any cells dependent on their target.  The set about doing this by using the Trace Dependents tool through VBA, and logically set it up to try to trace the dependent and react to an error if there wasn't one.  This is where things got interesting, as this object does not throw errors!

The routine below allows you to check a range of cells and it marks all those cells which do not have dependents.  The method is to trigger the Trace Dependents command, follow to the first dependent, then check the cell reference of the active cell vs the one that you started from.  If they are the same, then you haven't got any dependent cells.  If not, then navigate back and check the next cell.

[vb]Sub Marked_Dependent_Free_Cells()

Dim rngCheck As Range
Dim wsSource As Worksheet
Dim rngSource As Range
Set wsSource = ActiveSheet

'Record range to check (otherwise will be lost)
Set rngSource = Selection

'Check each cell in area to check
For Each rngCheck In rngSource

'Show dependent cells (if any) and navigate to the first one
With rngCheck
.ShowDependents
.NavigateArrow False, 1, 1
End With

'Check if navigation was successful
If ActiveSheet.Name = wsSource.Name And ActiveCell.Address = rngCheck.Address Then

'Still on same sheet, so no dependents found.
'Colour cell green
rngCheck.Interior.ColorIndex = 35

Else

'On new sheet, so navigate back
With ActiveCell
.ShowPrecedents
.NavigateArrow True, 1, 1
End With
End If
Next rngCheck
End Sub[/vb]

It was written and tested in Excel 2007, but should work in earlier versions. 🙂

How quickly things go sideways…

Posted on by
Reply

On Tuesday, I left work feeling a little run-down, but generally happy with the things that I'd got accomplished so far this week. I had a bunch of things planned out, and have been able to book time to get certain things done. Overall, the week was looking pretty good. From there, I went to a family picnic event, and progressively felt more and more run-down all evening, as a sore throat manifested. "Crap, I'm getting sick. I don't have time for this." was a recurring thought.

So Wednesday morning I wake up and feel like garbage. Headached, stuffed up head... I called in sick. I figured I've worked enough hours that I can do that, so I did... till my phone rang...

I don't believe that any news could possibly have been worse. The systems at work will let people log in, but they can't access any files. Damn... I drag myself out of bed, log in to work to check our file server... If I wasn't feeling sick before, I sure was about halfway through the day...
What follows is my notes for what has transpired over the last two days. I started taking these to make sure I wouldn't miss un-doing any steps that I'd done for testing, protecting, etc... It's long... about 6 pages, but gives an interesting glance into the lack of control you have as a tech guy when things go wrong, and just how much goes on before we make some of the critical decisions we have to make.

Oh... and because this is pretty much into the detail, I'll give you the readers digest version: The backup software corrupted our files and shut down our virtual file server. Once brought back up and all directory errors had been cleared, there was still corruption in individual files, so we restored our Property Management Software (records all of our sales transactions) to two days previous. The names, of course, have been removed to protect the parties, and all server names were changed.
‘****************

06/18/08 8:46 AM

  • File & Print (FP-Server) server is down.
  • Called IT Contractor for tech support (my main tech is out of town for a few days, so need them to find me someone else.)
  • Let IT Contractor know that I would initiate case with VMWare while waiting for a tech

06/18/08 9:15 AM – 12:00 AM

  • Logged case with VMWare. Case log follows:

**** Start VMWare Case Log ****
Problem:
-VM would not start citing inability to read a virtual disk snapshot file
Findings:
-opened up the vmdk descriptor files
-found that the snapshot CID is identical to its parent
-->therefore the parentCID referenced appeared to point to itself
-->however the parent file name was correct
-the parent snapshot did not match up with the base parent CID
-->the filename was correct here as well
-the FP-Server.vmsd file referenced an old filename that did not exist anymore
Resolution:
-hand edited the CIDs in the descriptor files and made up a new CID# for the child snapshot that had the same CID as its parent.
-edited the FP-Server.vmsd file to reflect the proper files
-started up the VM - all looked good
-deleted the snapshots through snapshot manager.Please follow this action plan before creating anymore ESX ranger backups or snapshots
1) make sure there are no snapshots
2) power down the vm
3) un-register it from VC
- right-click over VM -> un-register
4) delete the FP-Server.vmsd (this still contains old incorrect snapshot info)
- # rm /vmfs/volumes/SAN_VMFS_OS/FP-Server/FP-Server.vmsd
5) register the VM with the ESX host
- right-click over datastore in esxhost summary tab --> browse datastore
- browse to /FP-Server
- r-click over FP-Server.vpx -> register VM
6) migrate to desired server
7) start VM
**** End VMWare Case Log ****
06/18/08 10:00 AM

  • IT Contractor called. Missed call as on line with VMWare

06/18/08 12:00PM

  • Servers restored to production environment
  • Called IT Contractor to advise
  • Called all departments to advise

06/18/08 1:00PM

  • File system issues began surfacing
    • PMS Issue – Admin cannot post journal entry. Says “Disk is full”
      • Attempted to save file on the disk. Success
      • Referred issue to PMS Vendor
    • Excel Issue – Admin cannot save Excel file on server
  • Checked file permissions, all OK
  • Called IT Contractor.
  • Referred to email GM as contact was out of office. Did so.

06/18/08 1:38 PM

  • Called IT Contractor as no reply
  • My contact will track down GM

06/18/08 2:00 PM

  • PMS issues appear to be wider spread
    • Most departments cannot post sales. Nothing appears to happen. Affects
      • Marina
      • Pub
      • Clubhouse
      • Fitness Centre
    • Café CAN post sales, but receives “disk full” error when attempting to:
      • Print chits (bills)
      • Close chits (bills)
      • Print cashouts

06/18/08 2:56 PM

  • Called IT Contractor as no reply
  • My contact still trying to track down GM

06/18/08 3:02 PM

  • Spoke to IT contractor’s GM
  • New tech assigned to case (Tech's first day on the job)
  • Attempted to get Tech logged in to system

06/18/08 3:20 PM

  • Spoke to Tech at PMS
    • Specific files seem corrupt
      • Restored two files from Shadow Copy. GL able to post
    • PMS Tech ran a check over the Trial Balance. Out of balance by $2 million
    • PMS Tech expressed concern about file restoration
      • PMS is made up of hundreds of small files. Corruption seems to be in individual files
      • Concerned about restoring individual parts of whole, as it could cause issues
      • Can run a tool to identify corrupt files in installation
    • Recommending full restore from backup and rebuilding prior days
      • Means rebuilding a full day of sales transactions…

06/18/08 3:40 PM

  • In contact with IT Contractor Tech.
  • Get Tech logged in to our system
  • Discussed case to date and issues.

06/18/08 4:00 PM

  • Disabled logons to Citrix servers
  • Began online Chkdisk of all files
    • Online Chkdisk did not repair issues
  • Initiated offline scan

06/18/08 6:30 PM

  • Offline scan completed
  • File system corruption is gone. Logs clean.
  • Tests yielded same errors in PMS
    • Indicates specific file corruption still exists
    • Need to speak to PMS vendor, but Tech has left for day

06/18/08 7:00 PM

  • Advised all departments that system will not be on in the AM, and to be prepared to go manual for the next day

‘****************
06/19/08 9:00 AM

  • Had admin staff test opening/saving/closing files.
    • Issue with corrupt file in “06,30,2008 Deferred Revenue.xls”
      • Attempted to restore from Shadow Copy from 06/17/08 5:00PM. Corrupt
      • Restored from Shadow Copy from 06/17/08 12:00PM (noon). Restore OK
  • Admin staff attempting to print off yesterday’s PMS sales
    • Only department with transactions was cafe (no others could ring items in)
      • All chits are open because corruption prevented from closing chits
      • Cannot run end of day reports without closing chits.
      • Generating print screen captures of individual chits
  • Collected:
    • Posting journals from Tuesday (17th)
    • Posting journals from Wednesday (18th)
    • End of day updates from Tuesday (17th)
  • Placed call to PMS Tech and left message to return call

06/19/08 10:00 AM

  • Revoked permissions for “PMS Users” on PMS Share
    • Added “Accounting Users” permissions to PMS Share for testing
  • Disabled Logons to the following applications:
    • POS – Bev Cart
    • POS – Bqt Auto Grat
    • POS – Bqt Manual Grat
    • POS – Std Terminal
  • Enabled logons to Citrix servers
  • Called all departments to advise and request:
    • Servers back up for desktop/email use
    • PMS not available
    • Please open/save/close any files used
  • Placed call to PMS Support to track down (specific) PMS Tech
    • Requested to know if PMS Tech is in today
    • Support desk said they’d email him

06/19/08 12:00 PM

  • Placed call to PMS Support to track down PMS Tech
    • Requested again to know if Tech is in today
    • Made clear my system is completely down and issue is critical
    • Support desk discussing with Team Lead
    • Tech trying to track down anyone in Vancouver office
  • (Different) Tech connected and ran File Reconstruct Tool for audit purposes

06/19/08 1:30 PM

  • File reconstruct tool completed with one error
  • Called PMS vendor. Tech (same that ran reconstruct tool) connected and fixed error. Advised us to test again
    • Uploads to GL now work
    • POS workstations still report “Disk full”
  • Called and left message for (originally requested) PMS Tech

06/19/08 2:00 PM

  • Ran Trial Balance at May 31 to discover $2.2 million out of balance
    • Ran out of balance source journals for May. No out of balance entries
  • Ran Trial Balance at Apr 30 to discover $2.1 million out of balance
    • Ran out of balance source journals for April. No out of balance entries
  • Trial Balances as far back as May 05 are consistently decreasing in out of balance portions

Conclusion: Database is corrupted and any attempt to recover will be time consuming, costly, and suspect of defects

06/19/08 2:45 PM

  • Initiated restore from June 16 end of day backup of PMS system.
  • Pro Shop database (different system) knocked offline due to heavy throughput on server

06/19/08 3:00 PM

  • Originally requested PMS tech (finally) called back.
  • Advised to email him when restore was complete

06/19/08 3:16 PM

  • Restore complete
    • Advised Pro Shop to resume sales
  • Attempted to load PMS and received Activation error
    • Emailed PMS Tech (and got immediate callback)
    • Emailed PMS Tech logs of files that were not restored (as they were not backed up due to files being in use)
    • Re-Activated PMS software
  • Opened PMS and attempted to preview a trial balance report.
    • Error in preview
    • Certain Lib files were not backed up and therefore not restored
    • Re-installed activation software from CD as it copies Lib files onto system
    • Preview ran fine
  • Error logs from backup don’t indicate any other file issues per PMS Tech

06/19/08 3:30 PM

  • Initiated File Reconstruct Tool to ascertain that all data is in good shape

06/19/08 4:30 PM

  • File reconstruct finished error free
  • Begin testing phase internally
    • Tested posting journal entry – success
    • Tested posting A/P invoice – success
    • Tested posting A/R transaction manually – success
    • Testing automated upload of A/R batch – success
  • Prepared for wide deployment
    • Reestablished permissions on PMS directory for “PMS Users”
    • Re-enabled connections for POS terminals
  • Allow users back in gradually and alert to error potential
    • Advised Café to boot up, test and call if issues
    • Called Clubhouse and instructed to log on, make a sale and printed to kitchen – success
    • Called Marina to advise system back alive
    • Called Fitness Centre to advise system back alive

All systems appear to be functional at this time.

06/19/08 5:05 PM

  • Advised PMS, IT Contractor and internal managers of completion

‘****************

06/20/2008 9:00 AM

  • Will begin reconstruction of prior days data
  • 06/17/2008 from files
  • 06/18/2008 from hand tallied sales lists
  • 06/19/2008 from hand tallied sales lists

Cool Toys…

Posted on by
2

First off, I haven't forgotten about this, and am going to get back to it as soon as I have time. Between the extra pressure of being short-staffed again, all of my kid's year end activities, father's day and setting up a web site for my brother, I haven't had a lot of time to deal with it.

I did get a couple of new toys over the last few days though, that are pretty cool.

For Father's Day I got a new TomTom Go920 GPS unit. This thing absolutely rocks and:

  • Acts as a bluetooth hands free unit which links to my cell phone. My cell phone links to it as soon as I turn it on, and with 3 clicks I can call a defined contact. Very nice. 🙂
  • Allows me to update maps, including rename streets, update addresses and such
  • I can download different voices for it (like John Cleese) to give me my directions
  • Acts as an MP3 player

It's got a bunch of other features as well, and I'm still trying to figure my way through all of them. I've been wanting a GPS for a while, as it makes life a lot easier when I'm driving into an unknown city to teach an Excel class.

The other thing I got was a new cell phone, which arrived this morning. It's an HTC S640, which is a Windows Mobile SmartPhone.

I'm pretty stoked about this, as it is going to let me link to my Outlook data on the go. Lately I've been trying to manage so many things, and I often remember some of them on the way home. I can now pull over and add it to the list. (Or use the voice recorder to do the same.) Because we're using a Microsoft Exchange server, it links in quite easily (although I still have to do this part, so reserve the right to change my opinion on that,) and I'm going to be able to access my calendar, tasks, contacts and email. The bluetooth links seamlessly with the TomTom in the car, as I mentioned above, and I'm expecting the actual coverage to be 100% better than the Mike phone that I was using previously.

It's been less than 24 hours so far, but I've been wanting to get rid of the Mike phone for so long that this is probably the most exciting technology thing that has happened to me since... er... I bought a new server about a month ago. 😀

At any rate, I need to go work on my home server now, so that I can port my data there, reformat a PC that currently holds the data, and set it up fresh. I'm trying an experiment to see if I can get my wife the ability to book our personal appointments into my work calendar (without granting full access to work) so that I end up with a complete calendar on my SmartPhone. (I'd be lost without her, truly. I figure that this might help avoid her irritation when I keep asking her "what is on for tomorrow night" for the hundredth time!)

Job Opening at Fairwinds (again)…

Posted on by
1

Not too long ago, I posted that I was finally getting some help in my office from a new senior accountant. The person we hired was a great addition to our team, being intelligent and a good fit with our existing members. She caught a lot of stuff up for me, and this was allowing me to actually go home on time for a change. 🙂

Unfortunately, due to circumstances beyond my control, she's decided to leave us to return home to family and friends in Ontario. I did my very best to convince her to stay, as did some of our other team members, but it wasn't happening. I understand her reasons, and can empathize with her, but it still sucks. I genuinely wish her all the best in the future, and hope that the move gives her the support she needs.
Regardless, this is a certainly a blow to us, as we're just entering our busy season, and means that I'm back to advertising again. It also means that I'm bound to be putting in more hours until I can get the right person in and trained. 🙁

At any rate, as I'm looking for someone, I figured that I might as well post the job ad here... so here it is. If you are interested, or you know someone who would fit the bill, please send a resume through to us at the email address provided below.

Senior Accountant - Fairwinds Community & Resort

The Fairwinds Community & Resort is central Vancouver Island’s premier master-planned, oceanfront community with over 700 homes and homesites. It includes the award-winning Les Furber designed 18-hole Fairwinds Golf Club, the 20,000 square foot Fairwinds Centre health club and members’ facility and the 360 berth Schooner Cove Marina. For more information, visit www.fairwinds.ca.

Fairwinds is currently seeking an enthusiastic individual to fill the role of Senior Accountant. This is an exciting opportunity to grow your skills in a growing company whose activities span real estate development, food & beverage, golfing, and marine operations.

Your accounting functions will include:
• Creation of working papers and journal entries
• Review of working papers to support trial balances
• Generation of financial statements for review with the Controller
• Coordinating regular inventory counts
• Assisting with budgets
• Reviewing cheques for adequate support
• Review and documentation of systems internal controls

As the successful candidate, you will:
• Pay attention to detail
• Be able to recognize and solve problems of a technical nature
• Be enrolled as a student member in the 3rd or 4th level of an accredited accounting organization, (CMA, CGA, CA.)
• Possess in depth knowledge of Microsoft Word & Excel

Your accounting experience in one or more of our core industries will set you apart from other candidates.

Fairwinds also offers an excellent benefits package.

Please send your resume and salary expectations in confidence to resumes@fairwinds.ca. No phone calls or drop-ins please.

Only those selected for an interview will be notified.

Using Named Range to Refer to Cell Above

Posted on by
6

The last couple of times I've taught, I've received a question about how to deal with the following scenario:

  • You have a table of data, say in A1:A10
  • In A11, you have the following formula:  =sum(A1:A10)
  • Someone highlights row A11, right clicks it, and choose Insert Row

The effect of this, of course, is that cell A11 moves to A12, but the formula remains the same.  The user puts a new value into A11, and it isn't added in to the total.

This can be pretty frustrating, especially if the user who inserted the row isn't astute enough to check the formula.  One of my student expressed a great deal of frustration that she'd tried everything, even hiding the row above, but it made no difference.  (I'm not sure what else "everything" consisted of, to be honest.)

Of course, the reason hiding the row above didn't work is that Excel inserts a row above the selected row.  So the while the hidden row is above the totals row, Excel still inserts a new row just above the (selected) totals row, but below the (hidden) row above that.

At any rate, there is a trick that we can use to fix this problem: A defined name to refer to the cell above.  Here's how:

  • Open a new workbook
  • On Sheet1, select cell A2
  • Open the Name dialog:
    • In Excel 97-2003, you'd go to Insert-->Name-->Define
    • In Excel 2007, go to the Formulas Tab-->Define Name
  • Enter CellAbove as the Name
  • Enter =Sheet1!A1 in the RefersTo box
  • In Excel 2007 (Only), change the scope to Sheet1 from Workbook

Click OK when done to return to the worksheet.

The important points to note about this:

  • You were in cell A2 when you opened the dialog
  • You created a named range that pointed to cell A1 (not A2)
  • You stripped the $ signs from the range, making it relative, not absolute

So now, if you enter =CellAbove in your cell, it will point to the cell above.  (Unless you enter it in A1, at which point it will point to the very last row of your worksheet.)  Try it out.  Enter a value in the a cell, then go to the cell below and enter =CellAbove.  It should come back with that value.

The nice thing about named ranges is that you can use them in formulas.  So now, let's look back at our original issue.  We're looking at A11 containing the formula =Sum(A1:A10).  What would happen if we changed it to this:  =Sum(A1:CellAbove)

Curious?  Try it out!  Once you've got the formula set up, highlight row 11 and insert a new row (or ten rows, it won't matter.)  Enter some numerical data in the new row(s), and watch it add up.  🙂

The drawback of this is that you can only use the CellAbove reference on one worksheet, as it refers to that specific sheet when you set up the name.  (And using the same name on another sheet will overwrite it.)

There is a way to do it, but that will be the subject for another blog post.  🙂

Custom Number Formats

Posted on by
10

Over the last couple of weeks, I've led 3 sessions of my course on "Building Spreadsheets That Last". It's a course that focuses on good design practices in Excel, making things easy and intuitive for users, and generally teaches skills that I've used to set up my solutions so that they have the best chance possible of coming back to my in one piece when passed on to other users. The course always goes over very well, and I really have a great time teaching it.

One of the things that gets discussed, although only briefly, is custom number formats. This is more of an aside than a topic, but people always want to know about them when I quickly use one to display, say, a phone number. It usually ends up in a discussion, with me showing my favourite custom number format:

$ #,##0.00 F;$ #,##0.00 U;"-"??

(To set this up, right click your cell and choose Format Cells-->Number-->Custom, and enter the characters above in the Type box.)

Here's a look at what the output shows up like for a positive number, negative number, and zero, in that order:

customformat1.png

Cost accountants usually get this right away, but I'll admit that a few of my department managers have called me before asking what the "Eff You" is all about in the report. 🙂

The deal is that, when reporting financial numbers, it can be hard to really get the gist of whether a positive or negative number is a good thing, especially in variances. For example, revenues less than budget are a bad thing, but expenses less than budget are a good thing. This little custom number format removes the mystery, as it tells me that a positive number is "F" for "favourable". "U", of course, is "Unfavourable". (That would be favorable and unfavorable if you are one of my southern neighbours... err.. neighbors. 😀 )

So here's how this works...

The number format above is broken into three parts, separated by semi-colons. Those three parts work in this order: Positive Format;Negative Format;Zero Format. Using this logic, you can see that we have:

  • Positive Format: $ #,##0.00 F
  • Negative Format: $ #,##0.00 U
  • Zero Format:  "-"??

So what do these pieces all mean?

  • The dollar sign followed by the space is a dollar sign followed by a space. (Go figure.)
  • The # sign displays a numeric digit if one exists for that position
  • The comma, in this case, will only display if the characters to the left of the comma in the format exist. (i.e. It shows if the number is 1,000 or greater only)
  • The 0 will always show a numeric value, placing a 0 if one doesn't exist for that position
  • Because there will always be a leading zero, the decimal will show
  • The space followed by the F (or U) will display that text at the end

The Zero format is a little bit trickier, I suppose. It places a dash character, and pads the cell from the right by two full character widths. You could also place two spaces after the final quote, but the spaces are narrower, and you can't see them when you go back to edit your number format.

The great thing about custom number formats is that they don't change the underlying value on the cell. So even if my cell is displaying $ 56.00 U, it actually still only contains the value -56. This means that I can still base calculations off it, without having to fool around with stripping text and converting it to a value. Very cool. In fact, date formats work the exact same way, since all dates are really numbers. (Format your date as a number if you don't believe me.)

One question I got asked the other day was, "How did you learn to do this?" The answer is far less glorious than you might think... I simply went into the Format Cells dialog, clicked the number tab, and chose one of the many pre-defined formats that got me as close to my final format as I could get. Once I'd selected it, I then went to the Custom format area. By doing this, the current format was already in the "Type" box, so I started changing it gently, watching the output change in the "Sample" field above it, as shown below:

customformat2.png

The power in these formats is very cool, and I've used them to display things in a variety of ways. Phone numbers and SIN numbers are pre-defined in the Special section, but you can do Credit Cards with a custom format, for example. The possibilities are only limited by your imagination, really. 🙂