You know I’m a huge fan of Excel and all things spreadsheets. Pivot tables, complex equations, they all make my heart go pitter-pat. But I’ve never really understood databases. I can explain what they are and why they’re not like spreadsheets, but only at a very surface level. I haven’t ever grokked them because I never had access to a database program or found a problem they might solve.
We celebrate 13 years of podcasting (on the 13th), I tell the story of how my doorbell informed us that our UPS had died. We hear the interview with Boinx from NAB, and I’ll explain how Rogue Amoeba’s Audio Hijack solved two problems on an airplane. I get my Excel geek on when I solve a dumb question for John, and in a more practical tip, I’ll teach you about Snapthread for making videos out of Live Photos.
I have a spreadsheet that contains a 10 by 10 matrix of values. Each value is different. There are column headings at the top, ranging from 0 to 9 and there are row headings on the left, also ranging from 0 to 9.
What I want to do is this. I want to search for a value within the matrix, and if found, want Excel to tell me the row and column that it is in. In other words, if I search the matrix for ‘150’ and it is found at cell F7, then Excel can either tell me ‘F7’ or the row and column headings (I can work with either).
The problem I have is that all of my searching only results in information on how to go the other way (i.e. provide the column and row and ‘find’ the value).
The beautiful thing about this question is that a) it’s not at all dumb, it’s a surprisingly hard problem, and b) I had a lot of other things on my to do list (some of them fairly urgent) but of course I dropped everything to try to figure this out!
Fun with the Cool Blind Tech Podcast guys, My ScreenCasts Online tutorial is up about Parallels Toolbox. I was on DTNS Live and Steve captured some funny moments on video. I walk through all of the options for when you go to High Sierra and Office 2011 won’t work any more, centered around my love for Excel. I’ll explain my Bluetooth dropout problem and how I got it fixed. Then we’ll have a Tiny Tip about Previous Recipients in Mail.
I may have mentioned a few hundred times that I love Excel. I serious dislike PowerPoint, and I see no need for Word, but Excel brings me great joy. I simply love taking piles of data and turning it into information, and Excel is how I do that. There isn’t a pivot table I don’t adore, doing a vlookup delights me (once I re-remember how to do one), and simply formatting a graph to help explain my point brings me great joy.
If you find great value in any of the Office products from Microsoft, you may need to consider an Office 365 subscription yourself. You might be saying, “But Allison, Office 2011 does everything I need!” And while that’s true, you won’t be able to use Office 2011 after you upgrade to High Sierra. Microsoft isn’t going to upgrade it so you’re going to have to make a decision pretty soon.
Your choices boil down to this: Continue reading “There’s No Place Like Excel”