Drop Down List with Formula as Option

[jrtess01]

Hello Everyone,

I apologize if this situation has been answered in another thread, but I can't seem to find exactly what I'm looking for. Here is the situation I have (dumbed-down quite a bit) and what I would like to do:

Cell B5
="index(Model #'s Column,Match(Required Surface Area, Surface Area Column,1)+1)"

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Model #'s[/TD]
[TD="align: center"]Surface Area[/TD]
[/TR]
[TR]
[TD="align: center"]Mod1[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Mod2[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]Mod3[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]Mod4[/TD]
[TD="align: center"]150[/TD]
[/TR]
[TR]
[TD="align: center"]Mod5[/TD]
[TD="align: center"]300[/TD]
[/TR]
[TR]
[TD="align: center"]Mod6[/TD]
[TD="align: center"]400[/TD]
[/TR]
[TR]
[TD="align: center"]Mod7[/TD]
[TD="align: center"]700[/TD]
[/TR]
</tbody>[/TABLE]

So my formula in B5 will take an input of "Required Surface Area" and calculate which model is necessary to meet the requirements.

Right now I have this formula in B5, and I also have Mods 1-7 as a picklist in the same cell to give the user flexibility to override the calculation. This works fine for me right now except...

I want the ability to revert back to the calculated value once it has already been over-ridden.

Is there a way to do this? Let me know if something is unclear or more info is needed.

Thanks Guys!

 

[Smitty]

Welcome to the Board!

One way is to put your working formula(s) in an unused area, then record a macro copy/pasting it/them where you want. From there you can assign the code to a "reset/reapply formulas" button.

HTH,

 

[jrtess01]

Thanks Smitty,

I know this is a late reply, but I just got around to implementing this. I was trying to avoid using a button so I used a double click event instead! Works just like I want it to! Thanks for the advice.