 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2005Indirectly referencing a formula on a different sheet
I'm not sure if this is possible, so any suggestions welcome. I have a sheet that I use as a template which I then copy to create instances of this sheet. What I would like to do is to reference a formula for a cell on the template sheet rather than have a copy of the formula. This would allow the formula to be modified on the template and for all the instances to use the new formula. I had a look at the indirect function, but this only seems to allow strings to be converted to references. What i'm trying to do is indirectly use the referenced formula. Thanks Hi Andy, Without providing an example of what you want, your question is very vague with an answer ranging from a simple assignment to obtain the value of the formula on the other page to something involving the INDIRECT Worksheet Function which somehow ties into a formula on another page. By first searching websites via Google or newsgroups via Google Groups you would either find an answer or at least be able to ask a more focused question. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > I'm not sure if this is possible, so any suggestions welcome. > [quoted text clipped - 9 lines] > > Thanks Sorry, that was an attempt to try and keep the question simple as my application is rather complicated to explain using automation between visio and excel. If i have a sheet, called Sheet1, with cells A1 "No" A2 "Yes" A3 =AND(A1="Yes",A2="Yes") What I would like now is a sheet, called Sheet2 with it's cell A3 formula referencing the formula in Sheet1, A3. So any changes to the A3 cell formula in Sheet1 will automatically be picked up by the other sheets. Using INDIRECT only gives me the value of Sheet1!A3 call. Hi Andy, Place an = sign into the receiving cell on sheet2 then click on cell A3 on sheet1 hit enter and you will return to sheet2 with the completed formula it will look like =sheet1!A3 if sheet1 were actually sheet one you would see ='sheet one'!A3 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > Sorry, that was an attempt to try and keep the question simple as my > application is rather complicated to explain using automation between [quoted text clipped - 12 lines] > > Using INDIRECT only gives me the value of Sheet1!A3 call. Thats not what I am trying to acheive. The formula on sheet 1 references cells on sheet1. What I was trying to do was have sheet 2 reference cells on sheet 2, but using the same formula as on Sheet 1. Rather than have a copy of the formula, I wanted to reference it. Sheet 1 will be copied lots of times. I was hoping if the formula on Sheet 1 was changed, then the other sheets would then use that same changed formula. At the moment they have copies, so have to be updated individually. Hi Andy, To invoke the same formula used on another sheet. Function UseSameAs(cell) '-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel UseSameAs = Application.Evaluate(cell.Formula) End Function</font> sheet1!B4: 77 sheet1!C4: =5*B4 [displays 385] sheet2!B4: 88 sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440] To install the above user defined function (UDF) see http://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Andy" <andy.wagg,,@,,gb.schneider-electric.com> wrote... > Thats not what I am trying to achieve. The formula on sheet 1 > references cells on sheet1. What I was trying to do was have sheet 2 [quoted text clipped - 4 lines] > formula. At the moment they have copies, so have to be updated > individually. Sorry that isn't correct, it is taking the activesheet and not the sheet that the formula is on. "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote... >Sorry that isn't correct, it is taking the activesheet and not the >sheet that the formula is on. ? You mean it's taking values for cell references from the active sheet? Isn't that what it should be doing? As for your 'cell' argument, you should define it explicitly as a range object. It's specific to the worhsheet in which the range lies, not to the active sheet. Thanks Harlan, Corrected function uses parent "range object". Now use of Ctrl+Alt+F9 (Recalculate all cells on all worksheets in all open workbooks) can be used from any sheet without adverse effects. UseSameAs will use the same Formula or Constant as used in the referenced cell of another worksheet. Function UseSameAs(cell As Range) '-- Use the same Formula as used in the referenced cell '-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel UseSameAs = Parent.Application.Evaluate(cell.Formula) End Function sheet1!B4: 77 sheet1!C4: =5*B4 [displays 385] sheet2!B4: 88 sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440] --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm I think that that's still a problem. I created a workbook with 3 worksheets. In sheet1!a1, I put: 11 In Sheet2!b1, I put: 333 In sheet2!A1, I put: =Sheet1!A1+B1 In Sheet3!a1, I put: =usesameas(Sheet2!A1) In Sheet3!b1, I put: 33 I added application.volatile to the UDF. I put =rand() in C1 of both sheet2 and sheet3 I did a window|new window so I could show both Sheet2 and Sheet3. I selected C1 of sheet2 and hit F2|enter (to force a recalc of =rand() and a recalc of the UDF). Sheet2!a1 returned 344 Sheet3!a1 returned 344 I selected C1 of sheet3 and did the same: Sheet2!a1 returned 344 but this time sheet3!a1 returned 44 (what I wanted). If I changed the UDF to this: Option Explicit Function UseSameAs(cell As Range) '-- Use the same Formula as used in the referenced cell '-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel Application.Volatile UseSameAs = Application.Caller.Parent.Evaluate(cell.Formula) End Function I got what what I wanted: Sheet2!a1 returned 344 sheet3!a1 returned 44 No matter what the activesheet was when I recalced. (I like the application.volatile line in the UDF.) ps. I think you wanted this as your link: http://mvps.org/dmcritchie/excel/formula.htm#usesameas > Thanks Harlan, Corrected function uses parent "range object". > Now use of Ctrl+Alt+F9 (Recalculate all cells on all worksheets in [quoted text clipped - 19 lines] > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm  SignatureDave Peterson Hi Dave and Stephen, I'll go along with the Volatile it was getting too weird even for me since the formulas were on another page. Then I also made another change back to what I had originally tested with, as it otherwise gets a circular error if the reference is to a constant that looks like a cell address. Function UseSameAs(cell As Range) '-- Use the same Formula as used in the referenced cell '-- http://mvps.org/dmcritchie/excel/formula.htm#usesameas 2005-09-03 .excel Application.Volatile If cell.HasFormula Then UseSameAs = Parent.Application.Evaluate(cell.Formula) Else '-- needed if constant looks like a cell address UseSameAs = cell.Value End If End Function sheet1!B4: 77 sheet1!C4: =5*B4 [displays 385] sheet2!B4: 88 sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440] --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm I still think you need this version: UseSameAs = Application.Caller.Parent.Evaluate(cell.Formula) The unqualified Parent reference evaluated to the Microsoft Excel object when I set a breakpoint and added a watch. Application.Caller will represent the cell containing the =UseSameAs() formula. Application.caller.parent will be the worksheet that contains that cell. Application.evaluate() (same as parent.application.evaluate()) will use the activesheet for any range that isn't fully qualified in the formula. But worksheets("x").evaluate() (same as application.caller.evaluate()) will use that worksheet as the "home" for those unqualified ranges. I do like the check for the .formula, though. > Hi Dave and Stephen, > I'll go along with the Volatile it was getting too weird even for me [quoted text clipped - 22 lines] > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm SignatureDave Peterson Hi Dave, Sorry I missed that change, thought it was just the volatile. Thanks for the correction and explanation don't know how much of it sunk in but it's another step in the right direction and having correct code is what counts. Thanks -- David McRitchie Glad we got it close to right! > Hi Dave, > Sorry I missed that change, thought it was just the volatile. [quoted text clipped - 3 lines] > -- > David McRitchie SignatureDave Peterson No, I think it's perfect. I just meant I'm getting closer to understanding it -- though I don't know it yet -- another step along the way. "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message > Glad we got it close to right! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.