0

We have certain key metrics we would like to calculate on over 100 data entry forms. The metric calculations change from time to time. We would like to define one way to calculate the metric and have all data entry forms use that single definition. eg. Metric was QuarterlyExp/QuarterlySales =M5/M6, changed to Last4QuartersExp/Last4QuartersSales =SUM(J5:M5)/SUM(J6:M6)

I could use VBA User Defined Functions or PowerPivot DAX formulas, but my users would not be able to maintain these. Defined Names are on the edge of being acceptable, but it seems like the formulas would likely be worse than VBA!

Any ideas on how I could give them "one formula to rule them all"? All data entry forms will have the same logical values in the same cell locations.

TheRizza
  • 1,577
  • 1
  • 10
  • 23
  • I got your point but not entirely. A sample data with expected results depending on your scenarios would be of great help. – L42 May 06 '15 at 22:06

2 Answers2

0

Use the CHOOSE function to change your definition. A simple number will toggle your defined name formulas. See http://j-walk.com/ss/excel/eee/eee002.txt for an example.

David Hager
  • 106
  • 2
0

You could create a single sheet that performs all the calculations. Then you can provide a drop-down allowing the user to choose the metric they wish to seek, and use a formula to lookup the result of that metric on the calculation sheet.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37