Assign Variable To Worksheet Formulas

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.

Quote

Naming Constants

It is often when developing a spreadsheet that you will be constantly referring to a specific figure for calculations, such as a Tax Rate, Superannuation Rate etc. This often means either referring to a cell containing the value or simply typing the value in the cell itself. What we can do is instead of naming a cell or range of cells, is name a constant value. For example, you may be using calculations such as =(EmployeeRate*TaxRate)-8%, where the 8% represents the employers Superannuation contribution. We can easily replace the constant value 8% (0.08) with a more meaningful name. To do this:

1. Go to Insert>Name>Define
2. Type the name SuperCont in the Names in Workbook: box
3. Click in the Refers to: box and type in 0.08.
4. Click Add, then OK.

The two advantages of doing this are:

1. It makes our formulas easier to read at a later date.
2. We only need change the Superannuation Contribution rate at one central point for it to take effect thoughout our entire workbook.

Naming Formulas

As mentioned above Excel uses Absolute cell references for named ranges, this is normally what is required. But there might be times when a Relative reference would be better. Assume you have a list of 300 employee names in Column A, Column B has their pay rate per hour, Column C the hours they have worked. We would normally use a formula like: =$B2*($C2*24). Note we must multiply the hours by 24 to get the correct result, otherwise a time value like 8:00 would have a real value of 0.333333333333333 times this by 24 and you get a true value of 8. Lets replace this formula with a named formula. Note that the cell selection in this case only reflects a table set up as stated above.

1. Select cell D2 and go to Insert>Name>Define
2. In the Names in Workbook box, type in PayOwing
3. In the Refers to box type =$B2*($C2*24)
4. Click Add, then OK

In cell D2 type =PayOwing and copy this formula down and you will have the pay owing for each employee in the list. The very important point to note here is that we had cell D2 selected before we went to Insert>Name>Define and we used a relative row reference in our formula. This means that whenever we copy this named formula PayOwing, it will always multiply the cell two columns to the left on the same row by one column to the left *24.

Display More

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

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

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:

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