formulas from a drop down box

[Lisa H via OfficeKB.com]

I was wondering if it is possible to have a list of formulas in a drop down
box so that when I pick a formula, that is the one that is applied to that
cell. Any help would be great.

 

[Jim Cone]

Lisa,

The following uses a Combobox from the forms toolbar.
It assumes that Combobox has been assigned a single
column list range where the formulas are shown.
The first item in that list should be a title, such as "Formulas",
while the rest of the list should be the actual formulas without
the leading equal sign "=".
The linked cell for the combobox must also have been assigned.

'--------------------------------
Sub FormulaToActiveCell()
'Places selection from combobox in the active cell.
Dim rngFormulas As Excel.Range

'The combobox is named 'Drop Down 1"
With ActiveSheet.Shapes("Drop Down 1").ControlFormat
Set rngFormulas = ActiveSheet.Range(.ListFillRange)
'So you won't overwrite your own formulas.
If Application.Intersect(rngFormulas, ActiveCell) Is Nothing Then
ActiveCell.Formula = "=" & rngFormulas(Range(.LinkedCell).Text)
End If
'Reset display value in the combobox
Range(.LinkedCell).Value = 1
End With

Set rngFormulas = Nothing
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, USA


"Lisa H via OfficeKB.com"

 

[Lisa H via OfficeKB.com]

It kind of works the formulas I am entering look like this except the N2
values would change depending on what day it is (there are a total of 15
lines including "Formulas")
IF((n2/SUM(M15:M21)<1),n2/SUM(M15:M21),100%) - but when I click on one of
the formula options it gives me 200% or the next one gives me 300% as so on
down to 1500%. What am I doing wrong?!

 

[Jim Cone]

Why don't you highlight logical portions of the formula in
the formula bar and press the F9 key. That will give you
the values the formula is working with and should
help you determine what going on.
One issue, I can see is that "n2" should be N2.
If Excel was recognizing it as a cell reference, it would
be capitalized.

Jim Cone
San Francisco, USA

 

[Lisa H via OfficeKB.com]

Okay I went through and capitalized them all and checked the values they
are using and it's coming up correct is there something I am missing?
Unfortunatley I am a self taught Excel user and I just can't figure out
what I am doing wrong.

 

[Jim Cone]

You do not have to capitalize the entries. If Excel believes
what it sees is a formula it capitalizes the cell references by itself.
Since it didn't, that means there is something wrong with the entry.

One more thing you can do is verify that the cells, to which you are
transferring the formulas are formatted, as General not Text.

Jim Cone

 

[Lisa H via OfficeKB.com]

I know it took me some brain power but by George I think I have it working.
I was able to avoid having to pick a formula from a list, instead I nested
an Index function in place of the cell location and in that cell location I
just put in a data validation table so now I just choose what time I am
looking for and the formula recalculates itself based on what is in the
cell! Amazing what you can learn from using your brain.

Jim THANK YOU for all your inspiration and help!!!