An update to vlookup and arrayformula

For those trying to use arrayformula in Google Spreadsheets, I thought I would give a few examples that might help when designing your function.  In addition, I've provided a general example where you can fill in your corresponding ranges to build the function.  Let me know if you have any problems!

To customize use this key.

=arrayformula(if(row([range1])=1,"Impossible",vlookup([range1],[range2],{N}*sign(row([range1])),FALSE)))

where

[range1]: range of values to be looked up

[range2]: table of data where look up values are in column 2

N: number of column that has the data you want to return


I've also embedded a spreadsheet here that shows a few examples of how to use this:


Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template

Cheers!

Counting Unique Values of a Data Set

Here is a quick How-To from the Spreadsheets forum.  There are a few ways to do this in Google Docs but I wanted to highlight the use of the =query() function which uses GViz.  See these quick links for Google's Documentation:
Using =query()
Google Visualization API Query Language

I know "API" and "Visualization" and "Query Language" sound scary but they really aren't!  In this quick example I'll show you how to sum up data for unique values!

Consider the following data: Ted 5, Yogi 6, Adam 9, Ted 7, Adam 11, Yogi 13, Ted 6.
This example is meant for much larger data sets where there could be many different names of people with many different data points. Basically, the query() function will help us sum up the numbers next to each person's name by person and sort by the highest sum.


Breakdown of function from the center of the nest:
=query(A2:B8,"Select sum(B) pivot (A)") : This is the query used to collect the data that I would like. A2:B8 indicate the range of the data I'm going to query over. "Select sum(B) pivot(A)" says that I want to select the sum of column B by the values in column A. In spreadsheets world, this is basically a pivot table where you pivot on on the unique values from column A. This function alone puts the unique values (names) as their own column and puts the sum under it. However, most of the time it is easier to view this as the user in the post requested. See the Just query() tab above.


=transpose(query(A2:B8,"Select sum(B) pivot (A)")): This transposes the table. Basically it turns columns into rows and rows into column. See the transpose(query()) tab.


=sort(transpose(query(A2:B8,"Select sum(B) pivot (A)")),2,FALSE): The final step sorts the table by the summed column. The 2 indicates the second column in the table is the column to sort by and FALSE indicates to sort in descending order.

Happy Holidays and the coming weeks

Wow. I realized I posted ONCE in November. Not good.  Anyways, a lot has been going on with Docs and I hope to post more soon!  The past couple of weeks have been pretty crazy but I plan on doing a few more analysis posts about the forum itself, the future of Google Docs and try to apply some of what has been going on to a few models about change.  It would be really great to help users learn how to be the most resilient Google product users!  I do think some of this learning is going on by newer users but I hope a few of my insights will help others see their use of Google Docs from a new light.

However, I probably won't publish these posts for a few weeks (and hopefully from my new Chromebook!).  For now, enjoy this image I made using G+. Happy Holidays!!

The New Presentations Editor / Prelude to "The Google Way"

Just as a preface...I am slightly annoyed as I write this post...

The new Presentations editor has been available as an opt-in feature for the past few weeks now.  A lot of users have been posting about their experience so far but I wanted to make a few quick comments about what I've seen on the forums recently.

I am very surprised by the amount of negativity I've seen recently.  The new look for the Docs List has been out for 3 months, the new looks for all parts of the Docs Suite have been available for a while depending on which part of the suite you are using, and Google's global UI change is also being slowly applied across Google products.  For Docs, the newest roll out is the new Presentation editor.  One thing I don't users fully realize is that this is currently an opt-in feature.  This means that you don't have to use it and you have the option of using the old Presentations editor.  This is the Google way of doing things.  I have a post about my understanding of the Google way and whether or not I subscribe to it coming soon.  But for now, I want to make this point:

The new Presentation's editor is not in its final form and there is no reason to get worked up over a feature that isn't working correctly.  You are not being forced into using it hence the reason for the opt-in feature.  This is Google's way of a final testing for a new product.  They fully expect things not to work correctly and hope that users bring these issues to the forefront by way of the forums.  This is simply a preview of what the new editor is going to be like once the old version is no longer supported.  I think we can all agree that the new editor is a step up from the old editor.

My thinking about the new Presentations editor also applies to the newer Docs List.  This has significantly improved since the new Docs List was presented in early August.  While I have no idea how long it will take before the classic view is no longer supported, it is important to note that it has been three months.  Serious Docs users should be trying to get used to the new look because soon this will be the only look for the Docs List.

Google is in the business of pushing out innovative features that might not be completely bug free.  If they sat around trying to make everything perfect then the innovation would be curbed.

As a user of many Google products on a daily basis, I provide the following advice.  Get to know the product you are using as well as you can.  Be open to the idea that things will change and that Google does A LOT of user experience research to find out where they should take the products.  When something new comes to a product you use, try to embrace it because most likely what you are seeing is the future of the product.  Also, it will be to your advantage to provide feedback via the links you can find in the product or on the Help Forums.  I'm not sure about the other forums but I know the Docs TCs do their best to be on top of the forums, on top of issues that users are saying, and also provide our own feedback to the Guides. At least in Docs, the Guides truly respect our opinions and trust us to represent the users.

I don't mean to be negative but I feel that the mood of the Docs forum has been inappropriately negative lately.  It is much easier to help someone who comes to the forums who is willing to be helped as opposed to someone who comes to the forums to just complain.

Disclaimer:  The views represented here are my own are not necessarily those of Google.

Raw Materials Needed to Build Widgets!

KVarik recently asked a question about how to figure out how many parts are needed to create different individual items.  After discussing the issue, I soon realized that the outcome could be very valuable to anybody.  After reading this,feel free to use the template located here.

While I'm not exactly sure what KVarik was using it for I'll explain the template and the use in terms of a production line.  Let’s assume that Ted Company makes different kinds of Items.  Each Item is made from a combination of 10 different parts. I only have 10 different kinds of parts currently but the template can calculate up to 21 different parts.  Currently the template also only shows 10 different items but the number of items that can be applied is infinite.

So the spreadsheet allows me to decide how many of each type of Item I want to make and how many of each part I will need to make each item.  In order for this to work a user must indicate how many of each part are needed to make each Item.

One might think the solution was simple but keep in mind that each Item is composed of a different number of each part.  This means that the system had to be able to know how many of each part were needed for each Item and how many Items were being made.  I accomplished this by using a modification of Adam's arrayformula and vlookup combination.

Here is the function: =arrayformula(iferror($B3:B*vlookup(A3:A,'Item List'!A2:Y,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}*sign(row(A3:A)),FALSE)))

Now if you read the other post about using arrayformula and vlookup then this looks very similar with only two changes.  The first change I made was to the first parameter of the if statement.  Adam/Ahab's method creates a header row for the first row by explicitly defining what is going to go in each column header.  In this case the column headers are defined by other means so I only want to start the matching and multiplying process.  Therefore, I completely got rid of the nested if statement and created one iferror statement.  I still use the iferror() trick which involves only including the first parameter. Remember, that by excluding the second parameter, the function returns blank cells when it errors (in this case if doesn't find the item in the Item List).

The second change is that I added “$B2:B*” before the vlookup.  Think back to the days of linear algebra.  If you want to multiply a matrix (or array) by a value you simply indicate that value on the outside of the matrix (or array).  In this case I want to multiple the key array for each Item by the quantity I want.  So my output will look something like this:

$B3:B*{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}

Where $B3:B is the quantity of the Item

Where {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}is the key array from the Item List for the Item type in column A.

I suggest opening the template, making your own copy and changing the quantity on the Manifest tab.

Let me know if you have any questions about the template and have fun! 

Note: my normal convention is to indicate fields that can be edited without disruption of the spreadsheet with a yellow background and fields that should not be edited have a grey background.

Cheers!

Current healthz/404 Redirect Problem

There is a rather major issue right now affecting some users of consumer (maybe apps) accounts when it comes to accessing the Docs List.  The Docs List is accessed by clicking on Documents from your Gmail or by going to docs.google.com and signing into your account.  Users affected are being redirected to docs.google.com/healthz which results in a 404 Error.

For those of you posting on the forum, please know that Google is aware of the issue and trying to isolate the problem.

If you are experiencing the problem please post on the main thread that I have targeted here:


Healthz/404 Error Redirect Help Forum Thread

Rebecca is requesting IP addresses of those experiencing the issue.  In addition, please include whether you are on a free consumer account (@gmail.com @yahoo.com etc.) or Apps account (@yourdomain).

In the meantime, if you are unable to access your Docs List try accessing your browser history.  When you open a Google Doc this is like going to a webpage and each Google Doc has a unique URL.  You should be able to click on the URL from your browser history to access your Google Doc.


Just a quick note about writing on the forums:  I do my best to keep the forums organized but you will see quicker responses by posting in the correct section.  For example, this is a problem affecting the Docs List which means the appropriate place for a thread of this nature is in the Docs List section under Report a Problem.

Hopefully this gets resolved soon!  And always, if you have any suggestions or information you would like to provide about this problem please do so on the forum thread.  The more information the better!

Cheers!

Using =split() to create column headers: A "dripping awesome" result

Asker Anbotero asked the following question on the Docs forum yesterday:

"I happen to have columns A and B, each filled with “X”s: 

A     BX
        X
X     X
X
X        X
X     X
How do I count the number of cells filled in A that are not in B, and vice-versa? There is this undocumented Google Docs function, but I couldn’t understand how it works: COUNTUNIQUE().

For A it should be 3, and for B just 2."

Adam provides a wonderful and quick answers for finding the answer for column A:

=ArrayFormula( SUM( ( A:A = "X" ) * ( B:B <> "X" ) ) )

This formula must be applied in a column that is not A or B because his ranges use open ends. If this formula was placed in a cell in column A or B the formula would error as a circular dependency. His formula basically counts the number of times there is an X in A and not in B which is exactly what Anbotero was trying to solve. To amend this for column B it would look like:

=ArrayFormula( SUM( ( B:B = "X" ) * ( A:A <> "X" ) ) ).

So, while Adam was dutifully providing this answer I was trying to figure out how to do this with one single formula. Here is what I came up with:

Click here to make your own copy to play with.

The formula:

=arrayformula(if(row(A1:B2)=1,split("UniqueCol1xUniqueCol2","x"),split(countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"2")&"x"&countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"3"),"x")))

I'm more interested in explaining the method as to drilling down the function but if you want to know feel free to write in the comments and I will expand further. The function creates a 2x2 table. The first row basically takes the string UniqueCol1xUniqueCol2 and "splits" it between A1 and B1. The function knows to split when it sees "x", the delimiter, in the string. UniqueCol1xUniqueCol2 becomes UniqueCol1 | UniqueCol2.
The function knows to do this because of the first condition in the if statement. In general, if you want to create a row with headers always use =row() for the first condition in an if statement. Set this equal to the row number where you want the column headers to begin. In this example, I want the column headers to appear in row 1. Therefore, I use =if(row(A1:B2)=1...

The same is done for the second row but I don't need to define this row because I'm only using 2 rows in my range. Therefore, if its row 1 put my headers, if its not then do my calculations. For the calculations, the method of using =split() is the same. I do the calculation for column A, join it with x and then join it with the calculation for column B. Before the split, the function ends up returning 3x4. Then =split() does the same with 3x4 as it did with UniqueCol1xUniqueCol2.

There you have it! Yogi also just posted another method on his blog. All of these different solutions truly show how robust Google Spreadsheets can be (or how addicted the Docs TCs are!).


Cheers!


PS: Adam's query solution also works! See Sheet2 on my spreadsheet for that example.

New Docs List UI - Viewing Density

Ok so this is wonderful.  Today Google Docs is fully rolling out the new UI for the Docs List.  But the thing I am most excited about is Viewing Density!  You have the ability to choose how dense your Docs List appears.  This works by changing the amount of white space between Docs in the Docs List.  Currently there are three settings Comfortable, Cozy, and Compact.  Personally, I enjoy the least white space between Docs as possible (Compact View).  To change the viewing density click on the little gear in the top right corner (not the complete upper right corner).  The mouse over should say "Settings."  You can then choose your density and see a real-time change.

Enjoy!

Tip for Finding "lost" Docs

Occasionally users will not be able to find their Docs in the their Docs List. As much as people think Google does something to remove their Docs they rarely disappear because of something Google did.  Below are a few tips for finding these lost Docs.

1. Search for the Doc in the search bar in the Docs List by the title of the Doc (assuming you know it).
2. Search using "*" which will return all of the Docs in your Docs List regardless of whether or not you have chosen to "not show them in Home."
3. Check your browser history for recent viewings of your Google Docs.  Each Doc has its own URL which is saved in your browser history (assuming you don't delete browser history every so often).
4. Check the Trash.

Keep in mind that if you are NOT the owner of a Doc and the owner deletes it, the Doc is gone forever.

If you are still unable to find the Doc then maybe there is a problem on Google's end.  I've seen posts on the forums where Docs disappear only to reappear later. Sometime a little time shakes things out.

Cheers!

Methods of Depreciation: A Template

One of my classes in grad school is called Health Care Accounting.  We are tasked with concepts from Financial Accounting, Managerial Accounting, and topics from Financial Reporting.  Most recently we discussed methods of Depreciation.  In our context, we looked at depreciation in terms of how we allocate costs of an asset that has a usable life of some x years.  The template I created requires the user to input original cost of the asset, the salvage value (if applicable, otherwise $0) and the usable life in years.  The three methods I created the template for are Straight-line, Double-Declining Balance and Sum-of-the Years' Digits.

If you are interested in any of the functions feel free to comment!

Check it out!  This is also my first post to the Google Docs Template Gallery (which is kind of broken right now). Please rate it once its up! If you would like your own copy or to play around with it, simply choose File>Make A Copy from the view-only version below.

Enjoy!

Depreciation Methods Template

Reflecting on the TC Summit

This past week I was fortunate enough to go to Mountain View, CA for the Global Top Contributors Summit at the Googleplex.  The experience was truly amazing.  I got to meet Teresa C, the Google Docs, Sites and Apps Script Community Manager.  As a TC I am in daily contact with her about Google Docs so it was finally nice to meet her in person.  I also met many other wonderful Google employees.  We got to discuss the products with project managers, engineers and other support team members.  It was truly amazing how interested they were in what we had to say.  There were many situations where we would ask questions or have ideas and they would immediately contact someone to find an answer if they didn't know.  Google is extremely interested in what the users want and this made me extremely excited.  I really wish I could express how amped I am about Google, their mission and their products after going to the TC Summit.  I think they really understand people and how to make products that are always innovative.  I think this will make me be an even better TC.

To all who are reading this I hope you continue to stay with Google products.  I saw things that were truly amazing which proved how innovative Google actually is.

I was also impressed how Google treats their employees.  We all always hear about the things that go on at Google but the perks themselves are not really what impressed me.  Google treats their employees the way they do because they understand how to have productive employees.  If their employees are not being 100% productive then they believe they shouldn't be at work.  When this is the case, Google has provided activities that will help employees get to that 100% productive point.  Many people would think that the Googlers would take advantage of this but they do not.  Googlers are the type of people who know when its time to work and when its time to play.  I truly believe this why Google is so successful.  They pick the right people, put them in the right places, and provide them an environment to be productive, innovative and to explore the craziest ideas. Bureaucracy simply doesn't exist.

Before the TC Summit I was rather annoyed by the amount of complaints I saw from users and put part of the blame on Google.  I thought "Why would Google release a product with all of these bugs?!"  This led to my adoptions of "fixes before features."  But now I get it.  I know exactly why Google releases things knowing full well that it isn't perfect.  So I will be getting rid of the "fixes before features" tag in my signature soon.  However, I think my understand of innovation deserves its own post after a few more days of reflection.

So in the words of Yogi, let us keep Googling!

Cheers!

Destination: Grad School

I am starting Graduate School next week at Saint Louis University School of Public Health.  In two years I will hopefully be graduating and receiving my Master of Health Administration.  Fortunately, SLU uses Google Apps for Education which means I'll be doing some posts about how to use Google Docs in the classroom from a professor's standpoint as well as how I use Google Docs to keep up.  In addition, I am working for the School of Public Health on a new EMHA program.  I might do a few posts about how I use Google Docs for business uses as well.

Cheers!

iferror(1/0) in Spreadsheets

A common desire in Spreadsheets is to actually return a blank cell depending on certain conditions.  There are a few ways to do this, some that actually look like blank cells and aren't and some that are truly blank.

Consider this Forum Question.  Cherub96 wants the cell to actually be blank if his source cells for his calculations are blank.  Yogi's answer is perfect and is recommended.  BenFDC also chimes in with a similar answer that is also recommended.  Both answers give the exact same result.


The most important aspect of this post is that it is possible to return a truly blank cell in Google Spreadsheets!


The way to do this using the iferror trick.  This trick is done by wrapping an error expression in iferror and omitting the second argument.  An error expression is any expression that returns an error such as 1/0, sqrt(-X), NA() etc.  =iferror(1/0) has become common usage and is advantageous because you can easily create an array of blank cells.


However, there remains a bug whereby if, say, A1 is formatted as Plain Text, and text is entered in that cell, then =ISBLANK( A1 ) will return FALSE (as expected), but if the entry is then deleted form A1, then =ISBLANK( A1 ) still returns FALSE.  The cell is behaving as though it is housing a zero-length string, and presents another reason why we might generally resort to the LEN( ) test.


Notice, if cell A1 is formatted as Plain Text, a Text entry is made in the cell

=isblank(A1) returns FALSE, =Len(A1) returns 0 as expected

When Text entry in cell A1 is deleted

=isblank(A1) returns FALSE, =Len(A1) returns 0  not as expected

However, if A1 is reformatted as Normal and 

when entry in cell A1 is deleted

=isblank(A1) returns TRUE, =Len(A1) returns 0 as expected

Therefore, it appears the bug has to do with formatting a cell as Plain Text.

Happy blank cell making!

Thanks to Adam, André, and Yogi for their help with contributions on this post.

Cheers!

Arrayformula and Vlookup

There are a few formulas in Google Spreadsheets that do not work simply with the arrayformula.  However, with some careful manipulation it seems that it can be solved.

See the question asked by srdrum when trying to consolidate two forms.

Ahab came up with the formula and I did my best to alter and explain it to srdrum.  Post any questions!

Thanks Ahab for your example!

Cheers!

Vlookup & Image

In the spirit of being in Vegas I decided to put together a quick example of using vlookup and image.




Basically, just type the card in the yellow box or use the drop-down and you will see a picture of the card and also the total of your cards.

Here is a quick explanation of how the formula in B3 works.
=iferror(image(vlookup(B3,Lookups!$A$1:$B$13,2,FALSE),4,115,75),"Type card above")

Starting from the middle:
=...vlookup(B3,Lookups!$A$1:$B$13,2,FALSE)...
I am looking up the value in B3 which is somewhere between 2-10 or A,K,Q,J. Once that value is exactly found in my table,Lookups!$A$1:$B$13, I want to return the value in the second column of my table that corresponds with my lookup, which is a URL to the image I want. FALSE indicates that I'm looking for an exact match. If I don't find the value then the formula errors. Note: my table is on a hidden tab.

Out further:
=...image(...,4,115,75)...
This formula grabs the image from the URL specified (which I define based on my vlookup) and then I determine custom height and width. You indicate this by typing a 4 for the second parameter. For other options see the =image() function. I then defined my height and width.

The end:
=iferror(...,"Type card above"). Iferror looks to see if anything calculated within the formula has errored. If it has then "Type a card above" is returned. If not then the calculation itself is returned. The validation on the yellow boxes insures that the formula will not error (something being typed in that isn't on my list) but I added it anyway.

Sometime in the future I'll update this to make it a usable template with suggestions on what to do based on "the book". Feel free to play around with it in the template gallery!

Cheers!

Posting on the Google Docs Help Forum

In order to get the best response on the Google Docs Help Forum it is important to follow a few guidelines.

On the Post A Question page...

What to do:
1) Assign the correct Product
2) Assign the correct Topic
3) Type a short explanation or question

What not to do:
1) Do not arbitrarily assign Products or Topics in hopes that the Top Contributors (TCs) will find the question and put it in the correct place.
2) Do not type a long winded explanation of the issue.

On the next page...

What to do:
1) Make sure your have searched for your question.  Specifically searching the topic of your question.  If you spend 10-15 min searching around the forum you might get an answer faster than waiting for someone to reply because the answer already exists.  The is especially true for bugs.  Most likely somebody has already noted it.
2) Make sure you include the following information in your post:

• Browser & Operating System (e.g. Firefox/Mac OSX):
• Free consumer account (@gmail, @yahoo...) or Google Apps account (@yourdomain):
• Describe the steps you took that produced this error:
• Copy and paste any error messages:

This information is very important.  If you don't provide it a TC is most likely going to ask for it.  When answering the question about what type of account you have DO NOT actually put your email address there.

If you aren't sure about the type of account you have consider the following.  If you sign in at docs.google.com you have a personal account.  If you sign in somewhere else then you most likely have an Apps account.
3) Clear your Cookies and Cache before posting.  This can sometimes fix your problem and you might be asked to do this anyways.

What not to do:
1) Do not leave this section blank.
2) Do not type in all caps or violate any of online etiquette 
3) Do not complain, violate terms of service, or threaten.  Please understand that everybody is here to help and it is much easier to help someone who is easy to work with.  You will most likely find a quicker solution to your problem. 
By following these simple steps you should see a quick answer on the Google Docs Help Forum!

Cheers!

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!