Improve company productivity with a Business Account.Sign Up

x
?
Solved

Referencing forumalas across worksheets

Posted on 2009-05-12
4
Medium Priority
?
923 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
2 Comments
 
LVL 21

Accepted Solution

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

Open in new window

Retrieve-Remote-Formulae.xls
0
 
LVL 21

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

588 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