• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

Referencing forumalas across worksheets

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.
  • 2
1 Solution
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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now