Forum Discussion
Using drop down menus to multiply formulas with substituted text.
One possible way to make your formula dynamic is to use the CHOOSE and MATCH functions instead of SUBSTITUTE.
The CHOOSE function returns a value based on an index number, and the MATCH function returns the index number of a value in a list.
For example, if you have a list of engine types and conversion factors in cells A1:B5 like this:
Engine type | Conversion factor |
Small Diesel | 0.12 |
Medium Diesel | 0.15 |
Large Diesel | 0.18 |
Small Petrol | 0.14 |
Medium Petrol | 0.17 |
And you have a drop down list in cell F4 that allows you to select one of the engine types, then you can use this formula to multiply the mileage in C4 by the corresponding conversion factor:
=C4*CHOOSE(MATCH(F4,A1:A5,0),B1,B2,B3,B4,B5)
This formula will automatically adjust the conversion factor based on the selection in F4.
I hope this helps you with your task.