Referencing forumalas across worksheets
Posted on 2009-05-12
I'm working in Google Docs Spreadsheet and trying to build a workbook that will reference a master sheet for all of its various static/constant elements (e.g. a column with various cell that describe each row individually). I am doing this so that later on, if I need to change a formula in one cell or a description in one cell, I can do so just once and have it automatically update across the approximately 200+ worksheets that will be referencing this same master sheet.
For the static data listed above, I'm just using the "=ImportRange("key","sheet!cell") formula. This allows me to easily do most of what I need. However....
Here is where I need help. Along with several dozen static references using the ImportRange formula, I need to be able to reference formulas in the master sheet and rather than have the result of the formula be reproduced across each worksheet, I need the formula itself to be reproduced. E.g. If Master!B10 is referenced in cell Template!B10 and the formula in cell Master!B10 is "=SUM(B5:B9)", the result in cell Template!B10 to equal the sum of B5:B9 in the Template sheet and not the sum of B5:B9 in the Master sheet. Furthermore, I need the formula in Template!B10 to automatically update if I make a change to the "=SUM(B5:B9)" formula that is Master!B10.
Make sense? Ideally the formula/reference would be able to span across workbooks, but if needed, I could create 10 masters across the 10 workbooks that will be using this master/template system and then go through manually and update all masters. Ideally, it would reference a single master sheet...like the "=ImportRange("key","sheet!cell") formula does.
Thanks in advance for any help! I really appreciate it.