It is possible to “fix” or “lock” references to a single cell, row, or column. I always forget how to do this, and frequently need to look this up when I use a row at the top of a spreadsheet for holding static configuration values, such as an api login / key. So, I wanted to blog this both for my own future reference and for the benefit of anyone else who encounters the same problem.

Lock a Reference to a Column

Suppose you have a formula in D4 that references D1, and you want to lock the reference so that when you copy down from D4 the reference does not change to D2..Dn, but stays fixed to D1. Use the “$” character before the row number:

=D$1

Lock a Reference to a Row

Suppose you have a formula in D4 that references A4, and you want to lock the reference so that when you copy right from D4 the reference does not change to B4..n4, but stays fixed to A4. Use the “$” character before the column letter:

=$A4

Lock a Reference to a Single Cell

Suppose you have a formula in D4 that references A1, and you want to lock the reference so that when you copy down or right from D4 the reference always stays fixed to A1. Use the “$” character before the column letter and row number:

=$A$1

Here are a bunch of more tips and function reference materials for Google Spreadsheets: http://bit.ly/3ExvOx – not much on the locking cell references here, but some other killer stuff like “GoogleLookup.”

Back to hacking Google Spreadsheets…

Possibly related posts:

1. Google Dashboard Just Showed Me How Much Stored Value I Have with Google
2. vim tips ( via cheat gem )

Tangent: has anyone noticed how killer the Google “Preview” app is? You can view PDFs and PSDs right in the browser, copy and paste text from them–very slick.  Just more evidence that Google is my new OS.

Here are some highlights from my dashboard that I found interesting:

Account

Name: Andrew Kortina

Alerts
My alerts 7 active alerts

Calendar
My calendars 5 calendars – 1 public

Contacts
Contacts 1359 entries

Docs
Owned by me 301 documents
Shared with me 157 documents
Opened by me 348 documents
Trashed 51 documents
Starred 9 documents

Gmail
Inbox 6 conversations
All mail 39286 conversations
Sent mail 11698 conversations
Saved drafts 4 conversations
Chat history 4388 conversations
Spam 18 conversations
Trash 541 conversations

Google Sync
Google Sync 1 device configured

Profile
About me 4 entries
Profile URL: http://www.google.com/profiles/kortina
Links: 7 sites

Reader
Subscriptions 68 total
Starred 92 items
Shared 65 items
Following 20 people
Followed by 20 people

Talk
Contacts 51 contacts

Tasks
Task lists 1 item
Completed tasks 130 items

Voice
History 192 calls
Voice mails 8 voice mails
SMS 118 messages
Placed calls 4 calls
Trash 19 items

YouTube
Uploaded videos 34 public
Viewing history
Favorites 68 favorites
Playlists 4 public
Subscriptions 3 public

Other products
22 additional products are not yet available in this dashboard

What are your stats?

Possibly related posts:

1. Send To Tumblr And Track With Bitly Google Reader Custom Link
2. I Want “Read Flare” — Plugins for Sharing in Google Reader

It seems like Google has been doing more to make search temporally aware. More importantly, they have been doing a better job of educating me about this new dimension of search, driving me back to the one box with queries I may have previously performed via a more verticalized channel ( like the Eagles website or an app like Sports Tap).

I’ve also noticed spatially aware search results making their way higher up the one box results. Although the verticalized search I default to for this is also a Google product, Google Maps, knowing that I don’t need to go to a separate service to get results specific to my geographic location is an important and subtle behavior Google is training me to have.  Check out my Google results for “whole foods:”

localized Google search results for whole foods in nyc

Adding this kind of personalization is a constant reminder to me how good Google is at organizing the world’s information: in this case, they are organizing and making useful my personal spatial/temporal data. By constantly demonstrating the added value of personal data, Google just makes me want to give them more and more data.

On the one hand, it’s kind of scary how much Google knows about me. On the other hand, I see how much time Google saves me and I still want to keep exchanging data exhaust for time saved.

Possibly related posts:

1. Google Dashboard Just Showed Me How Much Stored Value I Have with Google
2. Send To Tumblr And Track With Bitly Google Reader Custom Link
3. Business Cards Have Google Maps Now

@arctictony : @aweissman @ewiesen. Have to disagree. Liberal arts may not work for a salary man, but it sure helps develop the innovation muscle.

@aweissman: @ewiesen depends how you define “education” – if by that you mean liberal arts education, I see low and declining ROI. Skillz pay the billz

@ewiesen: @aweissman I like Mish but I think that article is crap. Education might have poor ROI during an economic crisis, but pays in the long term.

@aweissman: “The cost of a US education seems enormously out of whack for what it provides.” Mike “Mish” Shedlock http://bit.ly/twOmf

Probably the greatest asset of the liberal arts education is an exposure to all sorts ideas and arguments across all sorts of domains. Your assignment, in general, no matter what your field of study, is to fully understand the author’s argument and then either express in your own words why the argument either holds true or is faulty.

Often, in a single class, you’ll read 4 or 5 different authors who answer the same question with a different answer. One author will build upon another’s body of work, but refute a key, perhaps subtle, point, in his predecessor’s argument, and then diverge into a new solution.  The trick for the student is to recognize the essence of the arguments, highlight the differences, and either argue for one side or propose a new argument and refute both.

Honestly, I believe that the liberal arts education is by far the most valuable of any you could choose as an undergraduate. Undergraduate study is very much about learning how to learn, preparing yourself to quickly absorb domain specific knowledge you’ll need for whatever job you find. I will always hire a sharp mind and a fast learner over a candidate who happens to have experience with a software suite or programming language we happen to be using.

The “learning how to learn” approach to undergraduate is not specific to studying the arts–you can come out of engineering school a fast learner, but something about the “use method X to produce result Y” rigidness of engineering always made this track more game-able to me. What I mean is that it was easier to slack on creativity in engineering coursework, to just go on autopilot, follow the rules, and arrive at the answer. The program was a system or game, and the rules were simple.

In the arts, to craft an excellent essay in response to a novel or treatise, you want to say something original–this takes creativity. And, the prerequisite is a deep, deep understanding of the body of work you are studying, which itself is not straightforward. You might have two experts on Aristotle with starkly different interpretations. When even the experts disagree, it’s difficult to just set your mind on autopilot cruising towards the solution, because there’s no one path to the answer and not just one answer.

Another, more clearly practical result of the liberal arts education is training in efficient communication. Since graduation, the biggest bottleneck I’ve seen in the business world is communication.  Often, you’ll read productivity essayists argue for writing shorter emails, reducing time spent on phone calls, running standing meetings. They only address half of the communication problem: clear, concise expression of ideas.  A liberal arts degree is probably the best way to train this sort of expression, especially when you have great teachers who force you to distill massive treatises into arguments of only a few type written pages of your own.

The less frequently discussed communication bottleneck is on the interpretation or absorption side.  Not everyone is an excellent communicator, and inefficiency on the side of the speaker / emailer translates to wasted time for the listener.  Hence, being able to instantly recognize where a slow discussion is driving, identifying the thesis early, is massively beneficial because it allows a sharp listener to interject and drive discussion towards the goal more efficiently by steering away from tangents or deep diversions into non-essential information.

I do not think I can express enough the value I get out of working with people who are great communicators–both on the expression and understanding ends. When you know you are working with a keen mind who will grasp not only your words but the goal you are driving towards, the purpose and intent driving your words, a sentence will suffice where a page would be required for a colleague not as adept at understanding motives.

I love working with expert communicators, and I think the liberal arts breeds them well.

No related posts.

Crossfit is constantly varied high intensity functional movement.

Functional movements are categorically unique in their ability to express power.

Power across broad time and modal domains is fitness.

Fitness measured across lifespan is health.

Affiliates are a distribution system for the above.

All of these are simple but broad declarations, but instead of relying on dogma, age, experience, or any sort of certificate as a reason Crossfitters should accept these tenets, Glassman always points to results and data derived from experimentation as the grounds for these core principles. “Study data, pay close attention to method, and ignore narrative” when reporting back new findings, he instructs affiliates.

The strength of the Crossfit way is that it is based on the scientific method: we recognize phenomena (elite fitness), form a hypothesis about why / how it happened ( training constantly varied high intensity movements ), and seek to duplicate the results through experimentation ( everyone does the same workouts in Crossfit and witnesses huge increases in functional strength, range of motion, endurance, etc ).

When I spoke with Glassman after the lecture, I thanked him not for Crossfit training program, but for his execution and for building such a strong community around the Crossfit organization. Crossfit for me is the quintessential 21st century org, something between business, government, and church, that exists only because it creates authentic value for participants and encourages lateral connections instead of a top down hierarchy. The affiliate model, group workouts, and competitive/game aspects of Crossfit do not simply motivate everyone to work harder and become friends with everyone involved: all of these things are the bedrock of a scalable business/organization/community.  The training method breeds new leaders and more members gain enough experience to teach newcomers, the capacity to handle accelerating growth keeps up with new demand.

Requiring participants to give back to the community (in this case via support and instruction) is what I think makes camaraderie so strong in organizations like Crossfit.  It is because you give back that you become vested, you care more deeply for those you help because they are realizing the fruits of your contribution.

I felt a strong void after leaving school and team sports; finding a place to give and receive help, to compete or work towards a common end, is how we fill this void. Others find a substitute in dogmatic groups (religious ones) or cultural communities (national pride/sports team based groups), but as science makes dogma more unacceptable and as global connectedness continues to erase national boundaries, the importance of lifestyle communities grounded in hard science is greater than ever.

This has been a bit desultory, but the community and methodology of Crossfit are truly inspirational to me. I look to this organization with gratitude for providing examples that help me make business decisions every day. Business in this century cannot afford to operate by dictating “needs” to consumers. The new foundation for scaling a large org is providing authentic value that inspires your community (formerly known as customers) so much that they cannot help but become vested in your cause and become your ambassadors, your teachers, and your leaders.

Possibly related posts:

1. So much of repugnant behavior is about getting something for nothing
2. Running Barefoot First Thoughts
3. Health = Long Lasting Functional Ability: The Evolving Crossfit Philosophy

Numeric Keypad Letters for Each Number

I used to look for a screenshot of the keypad of a phone every time I had to navigate a phone system that required me to type letters as input or tried to type a phone number that contained letters.  (On Blackberry, the number keys don’t have letters on them, so I could never figure out how you were supposed to navigate these alphanumeric menus and dial by name directories).

Today, I was without my screenshot, so I called @iqram and asked him to screenshot the Skype keypad and send it to me.  He asked me why, and when I told him he taught me a cool trick for blackberry. To mimic the tone correct numeric tone for a letter on Blackberry, just hit Alt and then the letter. Eg, typing Alt then A will sound the tone for the 2 key.

Worst case scenario: here is the complete mapping of letters to number tones for mimicking a touchtone phone in case this trick does not work for you:

ABC: 2
DEF: 3
GHI: 4
JKL: 5
MNO: 6
PQRS: 7
TUV: 8
WXYZ: 9

No related posts.

We are co-selecting for a whole bunch of admirable quality traits. Once you get rid of the people in your life, in your community, your gym, your neighborhood, who want something for nothing, who don’t understand that it’s through sacrifice and commitment and rigor, ardor, sweat, that good things come, things like thieves disappear. So much of repugnant behavior is about getting something for nothing. Crossfitters don’t believe that it is possible.

–Greg Glassman, excerpted from this recent Crossfit Journal Inteview on Primal Fitness. You really should watch this.

Possibly related posts:

1. Community and Camaraderie are the Foundations of 21st Century Business
2. Health = Long Lasting Functional Ability: The Evolving Crossfit Philosophy

Amazing app! I’ve been with my husband in Kansas from NJ while he’s military training and was stressed out about not being able to do some of the Crossfit workouts. I have 2 small children so I was totally missing our home gym until your app came along. The best $ I’ve spent at the store for sure. Would you be able to incorporate walking lunges into some of the WODs? They are a great alternative to regular lunges and definitely easy to incorporate into 4-5 rounds of a set. Thank you so much and keep up the great work! Lisa

Getting feedback like this can really brighten your day when you take a break from a morning of coding and see this in your inbox.  Something about the personal details about her family and children conveyed that there was a real person using and enjoying using this software–a bit different than watching Google Analytics numbers.

Possibly related posts:

1. On Power and Finals: Email to Penn Kempo

Since I read some articles on the deleterious effects of grains on http://wholehealthsource.blogspot.com/, I’ve been wandering around in a daze, feeling like my dietary world has been turned upside down. I became so confused about what was good for me and what was not that I ate less and less and felt more and more guilty.

It sucked. I lost weight.

But I’m recentered now. As I was trying to explain my confusion with grains, a friend reminded me, “Well, every food is going to be unhealthy by someone’s standards.” Oh yea. I had completely forgotten that.

….

Part of the problem I see with the paleo-type diet is that it seems to limit you to a “meat and 2 veggies” kind of meal. All the time. If I try to cut out all the sources of phytic acid from my diet, then I end up relying solely upon meat for protein, which – with all the fat and cholesterol – calls for a phenomenal amount of exercise… which is more than I do, more than I care to do, and more than I think is necessary.

If you look at a typical American meal (which I do every day in the cafeteria), the problem is that the bread or wheat product plays a central role. Pizza, pasta, sandwiches – typical lunch fare, all heavy on the bread. But if you think about what bread provides you – just look at the nutritional info on the package – it’s close to zilch. A bunch of starch, a bit of fiber, and cavities. In fact, it just looks like a filler. And maybe that’s how bread evolved anyway: to fill our bellies with cheap grains so we wouldn’t eat so much costly meat and vegetables.

….

You should read the full post here: Digestion – The grain dilemma.

I think Vishal is spot on with his recommendation to remove grain from the central role of a meal, reducing consumption rather than eliminating it. I personally, however, have trended towards Paleo and pretty much eliminated grains from my diet a few months ago.

I find that I have more energy, less after lunch lethargy, and I eat way more vegetables and fruits now. I also don’t feel super full and bloated after meals. One of the arguments Vishal seems to suppose that cutting grains means eating more meats, and he argues that he doesn’t exercise enough to burn these extra calories. I would argue this premise is bad: we shouldn’t be replacing grains with meats, but with veggies.  This will actually result in fewer calories in total and require less exercise for weight maintenance/loss.   I try to eat a small amount of meat at lunch and dinner, and cutting grains has not left me feeling deficient in protein at all, even though I have not been eating extra meat to compensate as Vishal suggests he thinks is necessary.

Though I feel much healthier having cut grains, I certainly feel a bit limited as Vishal says by the “meat and 2 veggies” meal plan. It gets kind of boring, and I think in the winter time especially I’ll miss things like lentil or black bean soup–delicious (and super cheap!) meals.

Possibly related posts:

1. Enjoy Salsa And Avoid Chips
2. KISS Recipe: Sauteed Broccoli
3. Must Watch Nutrition Vid

Placement and Language Tips for Share Links in Receipts

The call to share the deal with “your family and friends” is also deft.

Possibly related posts:

1. Send To Tumblr And Track With Bitly Google Reader Custom Link
2. I Want “Read Flare” — Plugins for Sharing in Google Reader
3. Understanding Direct Traffic as Traffic from Shared Links