3

I want to write a formula once in one sheet and have others call that formula and do calculation in their own sheet. I want to do this because calculations need to be done in 100 other sheets within the same document but I don't want to have to change the formula in every one of those sheets if we decide to modify it.

  • Do you mean other sheets within the same document or across different documents? – ChrisF Oct 7 '10 at 20:59
  • 1
    Within the same document. – dotLoom Oct 7 '10 at 21:17
  • In Excel to refer to a cell in another sheet you use: =Sheet1!B7 (for example) but I'm not sure how this helps you in your case. – ChrisF Oct 7 '10 at 21:29
  • Doing that doesn't bring the formula over it brings the value over. – dotLoom Oct 7 '10 at 21:30
  • 1
    If you associate your Web Apps and Super User accounts you'll regain ownership of the original question. – ChrisF Oct 8 '10 at 22:27
1

I am not sure I understand you requirement but perhaps a named range would help you. Take for example:

WA7923 example

Now name A3 - the default would be NamedRange1. This can be used elsewhere in the same sheet or in other sheets in the same document with:

=NamedRange1

to return 6 wherever entered.

Now say change + to ^ in A3 above. Then all those 6 entries will change to 16.

-1

Excel doesn't have a built-in to return the formula, you will have to use this user defined function.

  • Does anything like this exist for Google Docs? – dotLoom Oct 7 '10 at 22:39
  • No, Nothing built in or user defined, you'll have to build a script – Sathyajith Bhat Oct 7 '10 at 23:03
  • I was trying to build a custom function =CopyFormula() but it the scripting API forbids us developers from grabbing data from any cell other than the current active cell when using custom functions, so I doubt this will be possible in Google Docs – Sathyajith Bhat Oct 8 '10 at 0:44
  • Thanks for trying. How do I use this? code.google.com/googleapps/appsscript/… – dotLoom Oct 8 '10 at 3:10
  • @dotLoom This is what I had came up with, while trying to create that custom formula pastebin.com/qVcgBufV – Sathyajith Bhat Oct 8 '10 at 3:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy