Quick Tip: Viewing Formulas in Spreadsheets

Asker Lidiam recently had a problem with viewing the outputs of functions.  Lidiam was only seeing the functions and not the outputs.  This led me to believe that at some point Lidiam hit Ctrl+` on accident.  This is the keyboard shortcut for viewing formulas.  Ahab's post provides another method for viewing formulas.  Simply click View>All formulas and you will see your formulas.  Select this option again and you will see the formula output.

TC A.P.L (Adam) noticed that the user in Ahab's post must have been using Ctrl+' and not Ctrl+`.  The actual shortcut is Ctrl+` (the button next to the "1" key instead of a single quote).  Thanks Adam!

Cheers!

Jekyll and Hyde Accounts

In 1886 Scottish author Robert Louis Stevenson published a novella entitled [The] Strange Case of Dr Jekyll and Mr Hyde. The novella is famous because of its interpretation of Dissociate Identity Disorder, a psychiatric condition in which a person displays at least two distinct identities each having their own way of interacting with and perceiving the world.  In the story, the main character, a doctor, struggles in understanding the difference between good and evil. He spends a significant amount of time trying to repress his evil urges.  Dr. Henry Jekyll creates a potion which transforms himself into the evil Edward Hyde.  Throughout the novella, the Mr. Hyde personality grows in power and no longer relies on the potion to express his evil.  Eventually, the potion's role reverses and Dr Jekyll must rely on it to remain his normal "good" self.  As the potion runs out he realizes that he will be Hyde forever and chooses to take his own life.

With the many different types of Google Accounts, some of them are naturally going to develop personality disorders (I'm kidding...kind of).  There is a current known issue that has been given the name "The Jekyll and Hyde Problem."  The problem surfaces when someone with a free personal Google account shares the account name with a Google Apps account.  The Google Apps account can be any Enterprise, Education, non-profit, admin-controlled account.

Note: When signing up for a Google Docs account, you don't have to have a Gmail account.  I can use any email address to sign in at docs.google.com provided I have an account where the username is an email address.  However, if you use your gmail address for your personal docs account then you will never have the Jekyll and Hyde problem.

Here is how the scenario works:

I have a Google Docs account (which I created at docs.google.com) using my business email, lets call it Ted@tedjcompany.com. I log into this account by logging in at https://docs.google.com. This is my personal account which is the "Jekyll" account.  This account is called Jekyll because I haven't taken any potion yet so when people share docs with me using Ted@tedjcompany.com they are shared to my personal account.

TedJCompany decides to use Google Apps for business.  This means that TedJCompany now has their own dedicated domain called @tedjcompany.com.  Since I am the system administrator I decide that I want my employees to be able to use Google Docs.  To access the my business Docs account directly I will sign in at https://docs.google.com/a/tedjcompany.com.  The potion has now been consumed.  Any docs that are shared with Ted@tedjcompany.com will now only be shared with my Business account.  If I sign into my personal account via docs.google.com, I will not see any docs that are shared with Ted@tedjcompany.com.  This is the Hyde account because this account is the most powerful of the two and shall not allow the personal account to have and docs shared with it. The potion runs out and the Jeykll account becomes seemingly useless.

Instead of killing off both accounts, there is a solution! The system administrator should be able to resolve this if they carefully transition to the new infrastructure. They should carefully read all help articles below and follow the steps.  Ideally, before the transition users with potential conflicting accounts should change their account information.

Contact users with conflicting accounts

Resolve conflicting accounts (this includes information on how to resolve accounts before OR after migration to the Google infrastructure.)

Understand data sharing options

Please comment if you have any questions!

Cheers!

P.S. The book is free if you have a Kindle!

How to assign Hotel Rooms When Your Computers "Go Down"

I spent the weekend in Hinckley, Minnesota for my cousins wedding at the Grand Casino Hinckley.  The only problem we had the whole weekend was when we arrived.  We were told that the computers were down.  We were also told that we had requested two rooms, one non smoking room with a king bed and a smoking room with a king bed.  This was not the case because my brother and I were not going to be staying in a smoking room (we don't smoke) and with a king bed.  However, we were told that they had printed out the next 5 days of reservations.  I never really understood what was going on with their system but I eventually figured out the information that they did have.  They were trying to do all of this with paper and it was a huge failure.

A very simple spreadsheet could have solved the whole situation.  I created this spreadsheet below in about 10 minutes.  The data that the hotel had was a list of their reservations.  I also assumed that they have a list of all of the rooms in their hotel as well as the room type (smoking or nonsmoking).  If this information was dumped into my spreadsheet they could have easily assigned everybody that came to check in. Check out time was at 11am and check in time began at 4pm.  As a room was cleaned and available the housekeeping staff would indicate that the room was clean on the housekeeping tab.  All reservations and available rooms would appear on the main tab where the reservations could be assigned and guests checked in.



Feel free to make your own copy and see how it works.

It has since dawned on me that their computer systems going down must have been planned.  Why else would they have printed out the next 5 days of reservations?  There was clearly no disaster plan or even just a plan for an outage, albeit planned, like this.  For a large hotel/casino there should have been a system in place even if it was my measly little Google Spreadsheet.

Note: The point here was to show how quick something could have been created.  I understand that steps could be taken to improve the system with open ended ranges...

Also, when they said that they computer systems were down, they were only talking about their assignment and reservations system.  Their internet was working just fine.

How do I...? Create Dynamic Drop Down Data Validations

Data Validation with a drop down list has many uses.


On the help forums, VWH asked "Can I create dynamic popups?" VWH wants to assign individuals to certain tasks where each individual can only be assigned once.


In order to accomplish this we need an array of the available initials.  Next to this will be a column that checks to see if the person has already been assigned.

Notice, that the answer will be "Yes" or "No."  We will then query the table we just created to show only the values in column 1 (the initials) where column 2 is "No" (indicating that initial has not been assigned).  This new array will be what is used for the Data Validation with drop downs.  This array will dynamically update as initials are assigned.

For this to work the validation must allow invalid values (any values that isn't on the list).  If this is not checked the value you choose will disappear as soon as you choose it.  This is because the value is no longer on the validation list and therefore invalid.  Invalid values are not allowed and will disappear.  The only work around is to allow invalid values.

See my Templates Pages for your own copy!  Comments welcome!

Cheers!

Google Docs Vocabulary

When working with Google Docs it is important to use the correct terminology.  It will also help with understanding my blog and the help center. So here goes...

Google Docs - This is the term used in general for the suite of applications
Google Documents - the text editor from the Google Docs suite
Google Drawings - used to create drawings or diagrams for insertion into other Docs suites applications or publishing online
Google Spreadsheets - the spreadsheet editor from the Google Docs suite
Google Presentations - the presentation editor from the Google Docs suite
Sheet - a single spreadsheet/tab/worksheet
Spreadsheet - might be used interchangeably with workbook (note the difference between spreadsheet and sheet)

Often Sheet and Spreadsheet are used interchangeably as well.   I do my best to differentiate the two.

I try to use these terms on this blog and the help forum community also tends to identify with these terms.  You will often see questions by other TCs or power users asking to clarify so that we all understand how to help correctly.  If you are posting on the forums (especially for help with a Google Spreadsheet) it will help to be consistent with these terms.

Note: Be careful when using the terms "Google Doc" and "Google Document."  "Document" will always refer to the text editor while a "Google Doc" could be any of the four.  Most often you will hear, "Have you heard of Google Docs?"  This question is directed towards the whole Docs suite and not just a Google Document (text editor).

Have I confused you enough?

Cheers!

P.S. Thanks ahab for your comments!

Welcome to Docs for Life

I often get asked the following questions:

"Ted, how did you learn how to do that?"

"Ted, how do I [insert what they want to accomplish] in Google Docs?"

"Ted, why are you so obsessed with Google Docs?"

"Ted, can I have a copy of that Spreadsheet?"

The single goal of this blog will be to answer these questions.  Occasionally, I will ramble about Google Docs theory, best practices, or ways to get answers to your questions.

I am a Google Docs TC.  You can find me answering questions on the Google Docs forums.  As always, feel free to contact me via my Google Profile if you have a question about anything Google Docs related.  I will also expand on topics from the Google Docs forums.

I look forward to comments and questions!  Now go forth and CREATE!