Skip to main content

Dynamic cell references in spreadsheets with Google Docs

During my former internships in a consulting company I had to work A LOT with Microsoft Excel and often had to use dynamic cell references over multiple worksheets. Recently I started using the spreadsheets of Google Docs to track my bank account balance and to figure out where all my money goes. I decided to have one sheet for every month + one sheet of the month I want to analyze. But how do I dynamically change the reference to the sheet (the monthly sheet) I want to analyze without editing every single formula? Here is my solution:
  1. Create the target sheets and your overview sheet
    - I gave my sheets the names Month + Year (July 09)
  2. In the overview choose one cell that you want to contain the reference sheet and enter the sheet name
    - cell D24 in the example
    - Using the month names you might have to write 'July 09, otherwise Google will think it's a date.
  3. In the overview you can now dynamically reference to a detail sheet using the following formula:=INDIRECT("'"&&"'"&"!O2")
    - In my example =INDIRECT("'"&D24&"'"&"!O2")
    - For more details on the formula syntax read the
    Google Docs Help.
Try out my example. I wish the income would be real ;-)

Comments

Rock on -- this was exactly the explanation I needed -- Thanks!

Sean - www.emanaton.com
Anonymous said…
This post was very helpful! thanks loads!
Anonymous said…
Super advice, thanks!
Dominic said…
Thank you. Your post helped me.

Anonymous said…
YUSS.. Perfect Thank You