Conditional Cell to switch between Values in Discovery Wizard (Google Sheets Version)

Overview

If you need to show two different values or three different values while using a drop down then conditional formatting is of great use to you.

Step-by-Step Guide

Switch between Two Values

1. Add a column named As Is Value in your Excel sheet.

   Here, you can see that in the drop down you can select two options i.e. Profit and Percentage. When you select Profit, it will show values with dollar symbol and if you select Percentage it will show values with percent symbol.

2. Now add the IF clause as highlighted above.

   
   
3. Follow the steps to Add a Table in Discovery Wizard (Google Sheets Version).

4. Once you copy the table, you need to rename one column to any term that you deem fit.

   Here, rename Profit column as As Is Value.

5. Hover the mouse over the title and then click the pencil icon.

   Here, hover the mouse over the Profit column.

6. The Table Title Setting pop-up box appears. Now, replace the name with a title of your choice.

   Here replace the name Profit with As is Value.

,

7. Collect the code reference of the first cell of two columns.

   Here, copy the code reference of the Value column as well as the Percentage Profit column.

8. To copy the code reference, hover the mouse over the cell and click the Copy Code Reference icon and then paste it on your notepad.. Here, copy the code references of the values in the first cell of the Value as well as the Percentage Profit columns.

9. Now, hover the mouse over the first cell of the renamed column and then click the pencil icon.

   Here, hover the mouse over the cell in the As Is Value column and then click the pencil icon.

10. The Edit Cell pop-up box appears. Click Conditional Cell.

11. It expands to show another text box. Click inside the Handler Variable text box and paste the code reference that you copied first.

    Here, paste the code reference of the first cell of the Value column that you copied earlier.

12. It expands to show two more fields: Select Enum Option and Conditional Variable.

13. In the Select Enum Option text box, click to select the correct option.

    Here, as you are editing the Profit column, select the value as Percentage.

14. Click inside the Conditional Variable text box and paste the code reference that we copied second.

    Here, copy the code reference of the first cell of the Percentage Profit column that you copied earlier.

15. Click the Click to Add button and the entire box turns green in color to indicate that both the values have been added.

    
    
16. Click Save.

17. To ensure, whether the conditional formatting is working correctly, click the Preview button on the top navigation.

18. Please select either of the options to check whether the values are appearing correctly. If it is working perfectly fine, then click the Back to Admin button on the top navigation.

19. Follow the steps from 7 to 15 to do conditional formatting for the rest of the values.

20. Once done, you can delete the next two columns as they are no longer requited.

    Here, it is Percentage Profit and As Is Value.

21. Hover the mouse over the title of the column to be deleted. Click Row/Column Settings tab and then click Delete Column. The column gets deleted.

    
    
22. Follow the same steps to delete the next column as well.

23. The table looks compact now.

Switch Between Three values

1. Add the IF clause as highlighted above.

   Here, in the drop down you can select three options i.e. Amount, Profit and Percentage. When you select Amount, it will show values as number; if you select Profit it will show values with dollar symbol and if you select Percentage it will show values with percent symbol.

   
   
2. Follow the steps to Add a Table in Discovery Wizard (Google Sheets Version).

3. Rename one column to any term that you deem fit.

   Here, rename Amount column as As Is Value.

4. Collect the code reference of the first cell of three columns.

   Here, copy the code reference of the Value, Profit as well as the Percentage Profit columns.

5. Hover the mouse over the first cell of the renamed column and then click the pencil icon. The Edit Cell pop-up box appears.

   Here, hover the mouse over the cell in the As Is Value column and then click the pencil icon.

6. Click Conditional Cell and it expands to show another text box. Click inside the Handler Variable text box and paste the code reference that you copied first.

   Here, paste the code reference of the first cell of the Value column that you copied earlier.

7. It expands to show two more fields. In the Select Enum Option text box, click to select the correct option. Click inside the Conditional Variable text box and paste the code reference that we copied second.

   Here, as you are editing the Amount column, select the value as Profit. Now copy the code reference of the first cell of the Profit column that you copied earlier.

   
   
8. Click the Click to Add button.

9. The entire box turns green in color to indicate that both the values have been added. Besides, one more similar box appears.

   
   

10. In the Select Enum Option text box, click to select the correct option. Click inside the Conditional Variable text box and paste the code reference that we copied third.

    Here, select the value as Percentage and paste the code reference of the first cell of the Percentage Profit column that you copied earlier.

11. Click the Click to Add button.

12. The second box also turns green in color.

    
    

13. Click the Save button.

    
    

14. Click the Preview button on the top navigation to check whether the conditional formatting is working correctly, or not.

    
    
15. If it is working correctly, follow the steps to add the conditional formatting for the rest of the values.

16. Once done, you can delete the next three columns as they are no longer requited.

    Here, it is Profit, Percentage Profit, and As Is Value.