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:
Try out my example. I wish the income would be real ;-)
- Create the target sheets and your overview sheet
- I gave my sheets the names Month + Year (July 09)
- 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.
- In the overview you can now dynamically reference to a detail sheet using the following formula:=INDIRECT("'"&
- In my example =INDIRECT("'"&D24&"'"&"!O2")
- For more details on the formula syntax read the Google Docs Help.