x
Solved

# Referencing forumalas across worksheets

Posted on 2009-05-12
Medium Priority
923 Views
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.
0
Question by:zappero
• 2

LVL 21

Accepted Solution

alainbryden earned 500 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.

~Alain
``````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
``````
Retrieve-Remote-Formulae.xls
0

LVL 21

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.

--
Alain
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.