Assign Variable To Worksheet Formulas

  • Split from http://www.ozgrid.com/forum/showthread.php?t=102469




    Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.


    Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.


    Thanks in advance.


    PS: And I do consider myself above average for the sole reason that I know far more then every other person I have ever met in real life. And from that fact I can, in my opinion, safely assume that I know more then most people, hence I am above average. Not to say that I know everything or even a great bulk of everything or even 1/10 of everything, just more then the average person.

  • Re: Copy, Cut, Fill Relative Or Absolute Formulas


    Quote

    Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.


    It sound like you want to use a formula constant. See this information, available from this page: http://www.ozgrid.com/Excel/named-ranges-constants.htm
    Setup your formula in a cell, give it a name ("K"), and reference the name in your other cell formulas.


  • Re: Assign Variable To Worksheet Formulas


    Quote

    Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.


    One thing for sure, it has nothing to do with your choice of thread title: Automaticaly Insert/export Formulas


    I have lowered your assumed experience back inline with reality. I stongly suggest you read the big bold text on the New Thread page next time. [fa]*[/fa]

  • Re: Assign Variable To Worksheet Formulas


    Quote from AAE

    It sound like you want to use a formula constant. See this information, available from this page: http://www.ozgrid.com/Excel/named-ranges-constants.htm
    Setup your formula in a cell, give it a name ("K"), and reference the name in your other cell formulas.


    Unless I read that info wrong or I am just doing it wrong, all that allows me to do is turn 5 digits (.9685) into any other form. (I would pick "a" because that would make it simpler)


    So instead of typing =value*.9685 a million times I can now just type =value*a. Which is helpful, but not quite what I had in mind.


    As for the naming formulas, that looked like it might work but the functionality just isn't there. Refer to my next statements for what I was looking for. Thanks for the Naming Constants info though, if nothing else it's going to make it go by faster.


    Quote from Dave Hawley

    One thing for sure, it has nothing to do with your choice of thread title: Automatically Insert/export Formulas


    I have lowered your assumed experience back inline with reality. I stongly suggest you read the big bold text on the New Thread page next time. [fa]*[/fa]


    Not to be argumentative because I know you are trying to help, but that is not what I was talking about. First off my title was accurate, I do want into to literally insert a formula, which I now take to be impossible.


    Like, I type a number, and without doing anything to this number or typing anything else, BOOM! Predetermined formula inserted. I was thinking I could do something along the lines of "highlight cells>insert>formula>type in "=VALUE*.9685" (or using whatever syntax it might want)>enter/done" and whenever I would type any value into any of the cells I had targeted it would automatically insert the value I type into the VALUE variable in the formula and the insert the entire formula, or maybe just the answer, ether would work, into the cell.


    If this is doable, I am sorry I wasn't explicit enough to begin with to get my idea/question across, and if this isn't doable I wonder why... I'm sure this functionality would be useful in many situations.

  • Re: Assign Variable To Worksheet Formulas


    Quote

    Automatically Insert/export Formulas

    Quote

    Like, I type a number, and without doing anything to this number or typing anything else, BOOM! Predetermined formula inserted.

    You typing anything is not automatic, it's manual, and where on Earth you got "export Formulas" from is anyones guess.


    I really have no idea what you are wanting if AAE, 2 posts haven't helped you.

  • Re: Assign Variable To Worksheet Formulas


    Quote from Dave Hawley

    You typing anything is not automatic, it's manual, and where on Earth you got "export Formulas" from is anyones guess.


    I really have no idea what you are wanting if AAE, 2 posts haven't helped you.


    I type in a number and the FORMULA is "automatically" inserted. The export had to do with my other question, how to exactly copy over multiple formulas without the cell references being altered. IE, exporting the formula from point a to point b.


    I had made the title as short and concise as possible and at this point I feel your just looking for things to point out trying to make me look stupid/ignorant; Nether of which I am. I have spelled out my question in as simple a form as possible so that there can be no possible way to be misunderstood and all you can do is ask me to explain my, in my opinion, fairly obvious title. Maybe it took a little thought and stretching, but no where near as hard to figure out as you seem to be making it.


    And if you read my comments to his posts I tell you exactly why his posts don't answer my questions and then give as much information as possible, including thought out examples, to try and procure the desired information.


    This entire post was unneeded and only explained things that were obvious or had already been covered. If after this post you still do not understand what I am asking or just don't care to answer, then I'm out.

  • Re: Assign Variable To Worksheet Formulas


    Perhaps this thread would benefit by your uploading a sample workbook that includes examples of the formula(s) as well as shows the desired results. Apparently, what you have in mind is not getting clearly translated into these posts.


    Quote

    . . .whenever I would type any value into any of the cells I had targeted it would automatically insert the value I type into the VALUE variable in the formula


    This is the purpose of absolute referencing a defined input cell into which you type the variable. All formulas referencing the input cell are automatically updated. Using an input cell is efficient and is the correct way to apply a variable to all formulas that need it.


    Quote

    . . . and the insert the entire formula . . . into the cell.


    Any time you type into a cell it replaces the previous content, as you know. So, inserting a formula into a cell into which you just typed requires a VBA solution to replace the typed value with a formula that also includes the newly typed value. This is possible, but unnecessary since it is more efficient to setup formulas to receive inputs from other cells and immediately deliver the results.


    Quote

    I was thinking I could do something along the lines of "highlight cells>insert>formula>type in "=VALUE*.9685"


    If cell A1 is defined as the input cell for the "value variable" and you want this variable applied to a range of cells containing a number, say in B2:B10, then why can you not use a formula in C2:C10 that is "value variable * number", which equates to this: C2 = $A$1*B2 (copied down the range C2:C10). Simply entering a new value into A1 will change the outcome for all cell in the range C2:C10.


    Again, upload a sample workbook to make it clear to us, as it seems likely with the current trend of responses that the above is probably not what you have in mind.

  • Re: Assign Variable To Worksheet Formulas


    Export means data is exported to another File or Application and is not the same as "copy" which is what you are trying to say.


    Quote

    I had made the title as short and concise as possible

    "concise" should never mean misleading. I can see you DIDN'T read the links I posted.


    Quote

    I have spelled out my question in as simple a form as possible so that there can be no possible way to be misunderstood and all you can do is ask me to explain my, in my opinion, fairly obvious title. Maybe it took a little thought and stretching, but no where near as hard to figure out as you seem to be making it.

    Arh, I see, it is everyone's fault BUT yours and we are BOTH pretending to not understand your convoluted questions.


    AAE, has more patience with "above average" users, so I'll leave to him :)

  • Re: Assign Variable To Worksheet Formulas


    Hi Kindlin,


    I feel inclined to wade in here:


    To answer your original question: No, Excel does not have a "k" function that can be applied to cells. As has been alluded to, you can build an equivalent in VBA, though this would require more manual reworking than would make it worthwhile.
    Here is an idea of how that would look:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Empty Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 1 Then Target.Formula = "=" & Target.Value & "*0.9685"
    Application.EnableEvents = True
    End Sub


    This would do what you're asking, put into the worksheet.


    You've been getting "unhelpful" answers because:
    This is hardcoded to apply only to changes in column A - if you have a more complicated range you'd have to hard code all that in too. It is not tremendously robust, and if it crashes, it will leave events disabled... which could be remedied.
    It took a couple of minutes to write, far less time than simply writing the formula =A1*0.9685 in cell B1 and copying down would take.


    There are plenty of extra columns and it isn't bad practice to use them to "stretch" formulae. Also, if you use an extra column for what you're doing, you can see the original numbers at a glance and you can quickly edit them (i.e. select cell, type, instead of select cell, edit within formula, delete old number, type new number).


    I appreciate you're better than most you know; on this forum, sadly, you ain't got nothin'. If it helps soothe, consider yourself above average in the real world, but here... embrace the geek, there are plenty of people here with frightening knowledge.


    HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!