Solved

Referencing forumalas across worksheets

Posted on 2009-05-12
4
887 Views
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 sheet...like the "=ImportRange("key","sheet!cell") formula does.

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

Accepted Solution

by:
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.

~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

Open in new window

Retrieve-Remote-Formulae.xls
0
 
LVL 20

Expert Comment

by:alainbryden
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

697 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