0

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)

fve
  • 3
  • 3
  • 1
    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.: http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function – Adam Sep 03 '15 at 17:50
  • Thanks Adam, I will look into it. The solution could be something like: =INDIRECT(FORMELTEXT(Mother!C1)), but does not work. :( – fve Sep 03 '15 at 18:15
  • 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 Sep 03 '15 at 18:27

1 Answers1

1

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.

Community
  • 1
  • 1
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Thanks. Either I'm a bonehead or........ :) I open VBA, insert new module, copy paste those 4 lines in. I now have a "Module 1" Change Mother C1 to a string, and put =Eval(Mother!C1) into the cell. While I put this in, "Eval" shows up, so the function is "known", but I get #VALUE error. :( – fve Sep 03 '15 at 18:43
  • And if I click the fx, it shows me the string from Mother C1. Renaming Mother gives #NAME error, which I can't get rid of now. I'll, start over. :) brb – fve Sep 03 '15 at 19:09
  • Hmmm, if I remove the single quote from Mother C1, the cell January C1 calculates, but uses data from Mother A1 and B1 – fve Sep 03 '15 at 19:19
  • don't change Mother C1 to a string. Just put a single-quote in front of the formula. A single quote in front of an equal sign tells excel that this is just boring old text and to not evaluate it. Furthermore, in Mother C1, you put your `A1 + B1` formula, not `EVAL()`. In your other worksheet C1 is where you put your `=Eval(Mother!C1)` formula – JNevill Sep 03 '15 at 19:23
  • This way... you change the very normal formula in Mother C1 (just leaving a single quote in front of it) to... say... `A1+B1/C1+5` and then your other worksheet, because it has `=Eval(Mother!C1)` in it, will automatically change to that new formula. – JNevill Sep 03 '15 at 19:24
  • I did just put the single quote in. (I thought it made it a string, sorry) And now I'm back to getting #VALUE error. – fve Sep 03 '15 at 19:28
  • If I put in =Eval("Mother!C3") I get the formular shown as text. So it is grabbing it. – fve Sep 03 '15 at 19:32
  • 1
    Don't put quotes around Mother!C3 in the `=Eval(Mother!C3)` formula. That will make a difference since now the `Eval()` function is evaluating the text "Mother!C3" which is just "Mother!C3". You want `Eval()` to evaulate the formula thats actually in that cell... so no quotes there. – JNevill Sep 03 '15 at 19:35
  • Just tried in a simple spreadsheet, and it works. For some reason the formular I'm using is "wrong" or to complex, whatever. The formular works when copied in directly. Kind of baffled here. Anyways, the problem seems to be in the formular and/or formatting. (calculating time formats into numbers) Just for info the formular I'm trying to use is: =IF(OR((6<(E7-"18:00")*1440/60);(E7-"18:00")*1440/60<0);6;((E7-"18:00")*1440/60)) So problem is "solved" – fve Sep 03 '15 at 20:28
  • Well, then again. after going to the "live" sheet to copy the formular in here, the simple test, also shows #VALUE error. "A value used in the formular is the wrong data type" No changes made. – fve Sep 03 '15 at 20:34
  • Like.. after you put in your complicated formula in the Mother sheet, the other sheet with the `=EVAL()` formula throws a #Value? – JNevill Sep 03 '15 at 20:35
  • And making another test, which went well, now test1 is OK again. I can't make it fail now. This baffles me totally. But yes, I get #Value still on the complex formular. – fve Sep 03 '15 at 20:45
  • Ahhhhhhh, something does not reset. Playing around in the original sheet, making some changes, now both simple tests shows that 2+3 = 2014 – fve Sep 03 '15 at 20:48
  • If I go to test sheet, calculate now, result is correct, (5) I go to original sheet, calculate now, error on original sheet, and test result is 2014 – fve Sep 03 '15 at 20:51
  • And if I go to 1 og the "Mother" sheets and "Calculate now" (the complete book" both test sheets shows 2+3 = 0 OMG!!!! This is sooooo unreliable. – fve Sep 03 '15 at 21:07
  • Looking at this now. Your analysis in the original question is good. I have some ideas and I'll update my answer if they pan out. – JNevill Sep 04 '15 at 15:56
  • I have found a solution that doesn't add anymore VBA to the workbook, and insures that all of the calculations, regardless of what sheet you are on, are done correctly. The formulas are a bit more verbose, but I don't think it's anything that's too difficult since it's still better than updating 100 tabs with new formulas. – JNevill Sep 04 '15 at 16:58
  • This works. :) Only I had to change comma's to semicolons: =Eval(SUBSTITUTE(Mother!C1;"sheet";CELL("filename";A1))) (and change to danish) Now I only need to figure out why the formula(s) I'm using throw #VALUE when used this way. If I put in the formula directly into the cell, the formula works. – fve Sep 05 '15 at 06:15