Thank you for that information. We do not need a macro to solve the problem. However, you need to define exactly HOW the CPE is calculated. For the sake of demo, I use a VLOOKUP formula to use the selection under the 15-selection options in Step 1 about the 1-storey or 2-storey plans.
The logic here is to "capture" your selection and place the selection elsewhere. In this case, I used Sheet to as "supporting sheets". This means that the formula is built using Sheet 2 data.
Now, to capture the selected item in the 15-option radio box, you just need to select manually ALL of the items (15 items in step 1, plan types). Right click on the selection then click on Format Object. Screen shot below:
Click on Control tab, then in Cell link box, click on the small red arrow. Then it will minimize and you can now select WHICH cell to choose to hold the selection. In this case, click on Sheet 2 in the sheet tabs, then click on your preferred cell. In my sample, I choose Sheet 2, B2.
Then press Enter. Then click OK to close the dialog box. Now, B2 in Sheet 2 will hold the selected option among the 15 items. Thus, it will show numbers 1 through 15 depending on the selection.
Now, the trick. You need to create a table representing your "pre-selected" numbers. In your sample you mentioned you want to have 43.25 for the first option. I completed the table in Sheet 2 with my dummy data.
The clue here is that, when the customer choose an option in step 1, it will show in Sheet 2 B2 the option number. Then using the VLOOKUP formula, you can use the corresponding value in the table representing the rate that you pre-select.
Thus, in the sample I made, when you select option 1, the CPE will show 43.25. When you change the selection, the value changes as well.
Now, you can continue to revise the CPE formula by multiplying it by something depending on how you want the CPE is calculated. But in this sample, using a table and the VLOOKUP formula, you can use the selection to be processed in the formula.
Click here to download my sample:http://wikisend.com/download/437070/CPE 2_rev1.xlsx
Please remember to rate my service positively (3-5 stars/faces)
if this helped. A bonus or tip is always highly appreciated!
If you have any other questions, please ask me or reply to me – I’ll be happy to respond even after you rated.