how do you use formulas in a drop down box
 

I am trying to make a drop down box with formulas -- I have a spread sheet
with 15 plus tabs and the colums would be the same always but the numbers
changes -- i know when you paste special it auto calulates the cells -- So
can youdo this with the drop down box??? Example : @sum(a1*h7) or @sum(b1*f7)
thank you for all your help ...

how do you use formulas in a drop down box
 

With the help of a UDF......which you can copy to a General Module in your
workbook.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Enter formulas in a range as text. Note the apostrophes.

'=SUM(A1:H7)
'=SUM(B1:F7)
'=SUM(C1:G7)

Create a Data Validation dropdown list in M1 with that range as source.

In N1 enter =EvalCell(M1)

The "paste special" part I don't understand.


Gord Dibben MS Excel MVP


On Thu, 14 Aug 2008 12:21:02 -0700, Britt
wrote:

I am trying to make a drop down box with formulas -- I have a spread sheet
with 15 plus tabs and the colums would be the same always but the numbers
changes -- i know when you paste special it auto calulates the cells -- So
can youdo this with the drop down box??? Example : @sum(a1*h7) or @sum(b1*f7)
thank you for all your help ...