Monday, March 5, 2012

Excel Tips & Tricks

I enjoy spreadsheets and working with numbers.  A lot of this joy comes from the satisfaction of being able to keep track of many different (but similar things) and not have to tax my brain to remember minutia.  Below are some features that were complex enough to figure out so I'm going to document them here and I'll add to the list as time goes on!

Display sheet data based on a cell value
Displaying information from a cell on another page is easy =<sheet>!A1
At some point you might find yourself with a column of data, and want to reference a sheet that corresponds to that data.  Below is an image that will hopefully assist in the description.  Column D includes a bunch of stock options.  Beneath you'll notice multiple sheet.  In Column F I want to display data from the sheet that corresponds to the value in Column D.  To do that you need to use the INDIRECT function. Specifically something like this: =INDIRECT("'"&D5&"'!D4") where D5 is the sheet you want to reference and !D4 is the value on that sheet.