how do you use formulas in a drop down box

B

Britt

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 ...
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop Down with dynamic named range offsheet source list 14
Dependent drop down dynamic range formula, off sheet. 7
How do I create a drop down box within a drop down box? 1
Auto Change Formulas - Please Help. 2
Drop down box issue 2
Using drop-down lists 4
Protecting Sheets with drop down boxes 1
Multiple formulas in a single cell referencing a drop down box. 3

Top