Referencing forumalas across worksheets

Posted on 2009-05-12
Last Modified: 2012-05-06
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 the "=ImportRange("key","sheet!cell") formula does.

Thanks in advance for any help! I really appreciate it.
Question by:zappero
  • 2
LVL 20

Accepted Solution

alainbryden earned 125 total points
ID: 24540143
Here's a clever solution to your problem. Unfortunately, you would need to upgrade your workbooks to be macro-enabled, but odds are that isn't a problem if you're doing something as complicated as you are.

By creating a custom function for retrieving a formula, and a function for applying a formula based on either the contents of a cell or a String variable, you can pass a formula from the master to the templates.

Observe the three attached formulae and the way I have used them in the attached sample spreadsheet. You should be able to apply this concept to your task.

Function GetFormula(cell As Range) As String
   GetFormula = cell.formula
End Function
Function UseFormula(cell As Range) As Variant
    UseFormula = Application.Evaluate(cell.Value)
End Function
Function UseStrFormula(formula As Variant)
    UseStrFormula = Application.Evaluate(formula)
End Function

Open in new window

LVL 20

Expert Comment

ID: 26341550
I would really like to see my solution get solidified as the correct one in this case. It took me a while to put together and it's really quite effective.


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question