I need to be able to select one of 2 cells with a double-click, like a switch

[chrisneu]

My sheet is used to extract numerical values to column B from the pasted text into column A, and columns D, E, and F are used to add, subtract, or to multiply the entered values by selected percentage. Column C is used for alternative values, which are entered directly into the cells in the column. User then selects the column, either B or C, the values of which to be processed in D, E, and F, by double-clicking either the cell B2 or C2. However, I have a few problems with my sheet:

1. Cells B2 and C2 should function as "either-or" selection: double-click on B2, should put a bullet into the cell and clear the cell C2. Double-clicking C2 should put the bullet into that cell and clear the B2.
2. When empty, like the A6, columns D, E, and F should remain empty (unlike now as "#VALUE!" is displayed in D6, E6, and F6)
3. It has been working for me as it is, but it would be nice if D3, E3, and F3 were displaying decimal points: currently, they all display "2500" whereas it really should be "25.00". The problem is that the adjustment controls only allow integers and I was unable to get around it.

Thanks all in advance!

[rollis13]

1. change your macro do this, should do:

Please Login or Register  to view this content.

2. just add ISERROR to your formulas, example for cell D6:

=IFERROR(IF(B2="•",B6+(D3/10000)*B6,IF(C2="•",C6+(D3/10000)*C6)),"")

3. not sure how to change this.

[Artik]

See the attachment.

Artik

[chrisneu]

This is almost perfect! Thank you, Artik, rollis13. One more improvement that still remains:

with the cell formulas =IFERROR(IF(B2="•",B4+(D3/100)*B4,IF(C2="•",C4+(D3/100)*C4)),""), the columns D, E, and F remain empty when empty column B is selected. However, when switching to the empty column C, all cells in D, E, and F display "0.00". I understand that it is because the empty column B is not really empty and it is taken care of the IFERROR formula. The column C is empty and IFERROR, at least in the current formula, does not get picked up. I know that I can use the Conditional Formatting to hide the font color of the zero value, but is there a possibility to do it "the right way" rather than with the "workaround"?

[rollis13]

This in cell D6 will catch #VALUE! for B2 and 0 for C2:

=IFERROR(IF(B2="•",B6+(D3/100)*B6,IF(AND(C2="•",C6+(D3/100)*C6>0),C6+(D3/100)*C6,"")),"")

[chrisneu]

Perfect! Thank you very much!

[rollis13]

Thanks for the positive feedback, glad we were able to help.