Repeating formulas in Excel

[moo]

Hi,

I have a spreadsheet which has multiple sheets within it. Each of these sheets uses the exact same formula. So my question is can I get each sheet to refer to the formula once and once only so that if I need to change the formula I need only do so once instead of on every sheet.

If this is in anyway unclear then please don't hesitate to say so,

Advanced thanks,

Moo.

 

[DaleWatson123321]

moo,

I'd suggest you set up a "master" sheet (or "control" sheet) in which you have all those formulas which are the same on the other sheets.

If all the sheets are "identical", then of course you can use a copy of one of your existing sheets.

For the other sheets, change all your formulas to refer to the "Control" sheet. First change the control sheet's name (the sheet's "tab" at the bottom) to "Control".

Then for each of the other sheets, change each of the formulas to an "absolute reference" to the corresponding formulas on the "control" sheet.

To make the reference to the Control sheet, the formulas would be, for example... =Control!$A$1

If each of the sheets is "different", do the same as above, but ONLY include those formulas which need to reference the "control" formulas on the "control" sheet.

Hope this helps. Please advise as to how you make out, and whether this requires further explanation.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[euskadi]

OK, this is a wacky one, and won't work for every equation, but.... try it for fun!! (Really, you must).

Do I/nsert N/ames D/efine and add a name called
"myfunction"
and in the "refers to" section type this:

=EVALUATE(Sheet1!$A$1)+(RAND()*0)


Then type 'B2+B3 in sheet1, cell a1.


Now go to sheet2 and enter 3 in cell b2, and 4 in cell b3, and enter =myfunction in cell b5. You should get a 7.

Go to sheet3 and do the same (with different number), and you'll get a different result.

Now, go back to Sheet1 and change A1 to 'B2*B3 and presto, your Sheet2 function will now read 12!




Now configure to work in your workbook!