Nifty trick: switching rows and columns without breaking formulas

Back when I was talking about how to transpose, you may have noticed that I was only talking about transposing values, not formulas. Transposing formulas can be somewhat more tricky, and I mention this now because when I want to transpose formulas, it’s usually incremental formulas.

If you want to turn a column into a row, and the formulas in the column only have references to other rows, you’re good. A reference to one cell below will become one cell to the right, and one cell above will become one cell to the left. Likewise, formulas in a row that only refer to other columns will be fine, but if they refer to other row they’ll break if you try to transpose them. They’ll also break if they refer to cells too far outside of the destination range.

For example, imagine you have a list of people, and you want a bunch of tables with a bunch of statistics about these people. For some of the tables, there’s more people than there are statistics so you want one person per row. For others, you have lots of statistics about each person, so you want the statistics in the rows, and the people in the columns.

Fortunately there’s a fairly simple trick to fool the spreadsheet into doing what you want IF you’re only referring to cells in a different column/row than the one you’re trying to transpose from. Continue reading Nifty trick: switching rows and columns without breaking formulas

Project: Mass emails

A lot of times people need to email a whole bunch of people, like, for example, an entire department, and not everyone has access to a convenient mass mailer. So you need to get all the email addresses into the address field somehow. Typing them in is fine when there’s only a few, but the more names you have the more likely you are to mistype, forget one, or add one twice.

Plus, you can’t just have a list that you copy & paste into the address field. Many email programs require you to have either a comma or a semi-colon between addresses, and if you just copy the list you don’t have that.

So, here’s where another incremental formula comes in for me. Instead of adding, though, I use concatenate.

For example, assume column A has the emails, and column B has the formulas. Then assuming the first row has the column labels, the formula you’d enter in B2 would be =concatenate(A2,”, “,B3), or some variation as described below.

To illustrate:

A B C
1 Emails Formula Result
2 email1@here.org =concatenate(A2,”, “,B3) email1@here.org, email2@here.org, email3@here.org,
2 email2@here.org =concatenate(A3,”, “,B4) email2@here.org, email3@here.org,
2 email3@here.org =concatenate(A4,”, “,B5) email3@here.org,

See how the result grows? At the end, all you have to do is copy B2 and paste it into the address field. Your email program might not like the comma after the last email, though, so if it gives you an error message try deleting that comma and seeing if it fixes it.

And, although it’s kind of off topic, let me take this moment to remind people that if everyone on the list only needs to reply to you, not to each other, then bcc is MUCH better than cc. It means no-one accidentally sends a response to everyone by hitting “reply all”, and it means people don’t have to scroll down past a page or more of email addresses to get to the actual letter.

Variations:

If your email program needs semi-colons: =concatenate(A2,”; “,B3)
If you’re using Open Office: =concatenate(A2;”, “;B3)
or
=concatenate(A2;”; “;B3)
If you’ve named column A “Emails” and aren’t using Google Docs: =concatenate(Emails,”, “,B3)

Project: Calculating best money use

The other day someone asked me if there was an easy way to figure out how many of each of a set of things to purchase, to have the least amount of money left over. In this particular case, there was a choice between asking for per diem funds, or asking for overnight stay funds for conferences. I said sure! Based on the timestamps in the chat convo, it took me exactly 3 minutes and 5 seconds to have an answer ready. (It then took me 45 minutes to write this post about how to do it!) Continue reading Project: Calculating best money use