Snatch a formula from a Master Sheet

0
votes

I am building a spreadsheet with a lot of similar sheets, and a lot of formulas.

What I need is for 1 cell to grab a formula from a "Master" sheet, and calculate within its own sheet.

So if I change the formula in the Master sheet, it changes in all 400 places that formula has been used.

Ex: In the master sheet, cell C1 have the formula =A1+B1 In sheet "January" I put in data in A1 and B1 C1 then snatches the formula from master sheet, cell C1 and calculates. In sheet "February" the same happens. You get the idea.

OOPS, this is wrong! The formula should be =A1-B1 instead. (Dang, what an error) I now change the formula in the Master sheet, cell C1, and the correction is mirrored in January, Feb,......

Is this possible? (without VBA, macros and such)

How?

EDIT: The long story: Thank you for all your help JNevill, but I think this UDF has to be altered. Something is weirdly wrong, and I think I've found the problem. Here's a recap, for those who can't find heads or tails in the discussion. I have 6 sheets. 4 of them is the original workbook, and 2 sheets for testing. I have removed all "Eval" and inter-sheet references in the 4 sheets. Now sheet 1 "Formulas" consist of the shitload of formulas to be used. Sheet 2 consist a bunch of rates to be used. Sheet 3 and 4 is "2014 April" and "2014 May" now have the formulas in the sheets, not referenced. There's no references to any sheet in these 4 sheets.

Then I have the "Mother" sheet, with only '=A1+B1 in cell C1, and the "January" with 2 in A1 and 3 in B1 and the =Eval(Mother!C1) in C1. If I stand on "January" and "Calculate now" (workbook) the result in C1 is 5. If I stand in "Mother" and calculate workbook, the result in "January" C3 changes to 0, (remember "Mother" A1 and B1 are empty) If I go to either "2014 April" or "2014 May" and click calculate, the result in "January" C1 changes to 2014 (in both sheets A1 is 2014 and B1 is empty) If I go either "Formulas" or "Rates" and calculate, the result in "January" C1 changes to !VALUE. (in both sheets A1 is some text, and B1 is empty)

Same thing happens if I goto "January", calculate (result is 5) goto "Mother" save and close, spreadsheet open in "Mother" and result is 0.

So: The "Eval" formula in "January" C1 is referencing A1 and B1 in the sheet I just happen to be standing in, and not "January" A1 and B1

This is NOT desirable behavior. :)

(no wonder, I'm confused)

1
excel
As @Jnevill demonstrates below, this is actually a pretty easy task with macros. I think it can be done witout macros, but it will be much more complicated. This page may help you get started.: myonlinetraininghub.com/… - Adam
Thanks Adam, I will look into it. The solution could be something like: =INDIRECT(FORMELTEXT(Mother!C1)), but does not work. :( - fve
The method in that article doesn't use indirect. It uses evaluate() within a named range definition and it will be very messy. The VBA solution is very simple, and is a great way to start learning VBA. Unless you have a reason you cannot use VBA (company security policy, or something like that) you should follow the advice @JNevill gave. - Adam

1 Answers

1
votes

This uses some VBA, but nothing major... I promise. Like... seriously, it's a single UDF with 2 lines of code.

You can use this custom UDF called "Eval" that should really just be baked into a real formula by Microsoft, but... maybe in Excel 2040.

Anyhow... that allows you to turn a string into a real live formula. Grab that Function from the first answer there and stick that in a new VBA module.

In your "Mother" sheet, cell C1 stick '=A1+B1 in there (Notice the single quote before the formula... you want this to be a string)

Now in one of your inhereting sheets use the formula:

'=Eval(Mother!C1)'

Now that thing will fetch the cell value from Mother!C1 which is our text/string value of a formula. That formula will be processed by our Eval() function in VBA and it will... evaluate the thing as if it were a formula, spitting out whatever A1+B1 is on that worksheet.

Updated

So it looks like the eval() UDF has some issues, wherein if the workbook calculates while on a different tab then the one on which the eval() function exists, the eval() function will calculate using the values of the active sheet. Which is bad.

Here's one way around that, and it makes things a bit more complicated, but I don't think it's outside the realm of reasonableness... and it doesn't add anymore VBA, which is a plus for this question.

In the Mother!C1 cell, change the formula to '='sheet'!A1+'sheet'!B1; remembering the extra single-quote at the beginning of the formula, so it's treated as a string. This may seem odd since you don't have a tab named sheet but we are going to treat sheet as a token to be replaced in the other worksheets.

In the other worksheets, in C1 put the formula: =Eval(SUBSTITUTE(Mother!C1,"sheet",CELL("filename",A1))) You'll notice this got quite a bit more complicated, but all we are doing here is replacing the token sheet from Mother!C1 with worksheet name (that CELL("Filename",A1) actually brings in the full file path of the workbook as well as the worksheet/tab name, but it works fine for this). So now the formula that is passed to Eval() will look something like ='C:\Your\File\Path\[Workbook name.xlsm]Sheet2!A1 + C:\Your\File\Path\[Workbook name.xlsm]Sheet2!B1', which is pretty verbose, but excel doesn't care and it will process it very quickly.

You'll find now that because Eval() is evaulating a formula that has the the worksheet names as part of the cell reference for whatever sheet passed Eval() the request, that the formula returns the expected result.

If I had to do this for my own workbook, I would probably rely more on VBA. I would do a Worksheet_Change() event on Mother that would then trigger the updating of formulas on all other tabs in the workbook. If you are unfamiliar with VBA though, or the thought of having to write that and debug it is overwhelming, I think the Eval() UDF is still a really solid route.