Select multiple independent cells and perform a calculation

This is a two part question.

Firstly, is there a way to select two or more cells that are independent from each other? (e.g. "A2" and "H6")

Secondly, after the two or more cells are selected is it possible to perform an instantaneous calculation in another cell? (e.g. "A2" shows 5 and "H6" shows 10 then "M27" displays 5+10=15)

Thanks.

Thanks for the response. is there anyway that I can use just the mouse rather than having to hold down the Ctrl key. I imagine a bit of VBA code.

Any ideas on the code to display the sum in a cell?

Thanks

Not sure if this works in excel 2010 but it works in 2007.

First enable iterative calculation (Excel options > formulas) this will supress those annoying circular reference warnings that you will see while setting this up.

Next go to the formulas tab on the ribbon, then Name manager > New

Name:- Selection
Refers to:- =SELECTION()

In the cell where you want the result, enter =IF(TODAY(),SUM(Selection))

** note that the IF(TODAY() part is required to make the formula volatile so that it will recalculate.

Select your cells using the ctrl click method suggested by Palmetto, then press F9 to force the sheet to recalculate.

Originally Posted by mtsykes28

is there anyway that I can use just the mouse rather than having to hold down the Ctrl key. I imagine a bit of VBA code.

Combined with the code supplied by tigeravatar in your other thread as I'm guessing the 2 are related.

Double click the first cell to activate multiple cell selection, then single click the others. Double click again (any cell) to deactivate.

List of selected values will show go in G24, the sum in G25.

Please Login or Register  to view this content.

Thanks Ben and Jason.

The code from Jason works like a charm however at the moment it only adds. Is there a way for it to be able to add and subtract depending on what columns and cells are selected?

For example:

If I select a cell in column A first then a cell from column B second it will be an addition.

If I select a cell in column B first then a cell from column A second it will be a subtraction.

At the moment you have to double click the first cell and then select the non-contiguous cells which adds them up as they are selected. Would it be possible to triple click the first cell and then select the non-contigious cells as normal however this time it would subtract?

Again your help and advice would be very much appreciated.

Would your examples always be the case?

I can edit the code to add or subtract depending on which column you double click, but I don't think that there is a way to trap a triple click.

The following are the actual column combinations:

ADDITION: B then F, G then K, L then P, R then V, W then AA, AB then AF

SUBTRACTION: F then B, K then G, P then L, V then R, AA then W, AF then AB

I hope that makes sense. Shame about the triple click - that would be a brilliant feature.

Thanks for all your help so far.

Couple more questions to try and get it right first time.

Will you be selecting multiple cells per column, or more than one pair of columns at any one time?

Where do you want the results, should it always be same place, or relative to the selected columns?

Yes I probably will select multiple cells per column however one cell from each column will be the most common method.

Is it an either or answer to re-working the code with regards to selecting a single cell per column or selecting multiple cells per column?

The results will always have to be displayed in the same cell.

Thanks

A slightly different take on the solution, see if this works for you.

Double clicking a cell in any of your listed columns will carry out the relevant addition / subtraction and place the result in G25, repeated double clicks will append to the existing total. Double clicking a cell in any of the columns between will reset G25 to 0.

Please Login or Register  to view this content.

This works really well and keeps the spreadsheet discreet. However in the code you sent before it showed two cells - one with the selected cell and the other with the sum total. Is there a way to include this because at the moment the spreadsheet cells are rather small and I use that cell in the spreadsheet to show the selected cell enlarged.

Also the current code only allows directly opposite cells to calculate.

B2 and F2 = addition or K2 and G2 = subtraction

Is there a way to be able to chose cells that are higher or lower in the column?

Example:

B2 and F6 (addition) or K3 and G9 (subtraction)

Hope this all makes sense.

Again thanks for your continued advice with this problem. It is much appreciated.

I think this should work, I've tried to keep it simple with comments showing what you might need to edit.

Please Login or Register  to view this content.

Note that I haven't included any error handling in the code. If the double clicked cell, or the related offset cell conatins text then the code will fail. The same would happen if the offset related to the clicked cell refers to an invalid range, i.e. if your row offset was -4 and you double clicked in row 3.

Hope all that makes sense.

Sorry I have not got back to you I have been away for the past week and have only just looked at the code today. I have run the macro and have read your comments and changed a few things however I cannot get the the double clicked cells to subtract with any other selected cells they can only add. Any ideas?

I have decided that it is probably best for me to just be able to select a specific cell (say B3) then seperately select another cell (say F5) rather than it just be automatic. I hope that makes sense.

Cells selected in columns B, G, L and Q first need to make an addition calculation.
Cells selected in columns F, K, P and U first need to make a subtraction calculation.

Thanks for all your help so far. Much appreciated.

I think I finally have this right

Trying to keep it as close to the method in post #6, Double click in one of your specified columns to start, then single click the remaining desired cells to add / subtract the values. Single click in a 'between' (C,D,E,H,J,K,etc) column to stop. The results will remain in the sheet until next time you double click.

Please Login or Register  to view this content.

The code will add a "-" prefix to subtracted values in the cell where the values are listed, if this is not required, replace the line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I think it should do what you need it to, guess we will find out shortly

Working! Adding and subtracting perfectly depending on the columns that are selected first. Brilliant.

Two quick questions:

Is there a way for the calculations cell contents to disappear when finished? When you double click on another column to stop the calculation in the previous code you posted the cell that showed the calculations cleared to show an empty cell. At the moment when you double click when finished the calculations remain. Is it possible to have this feature in the new code?

Also I have decided to put the calculation box now in A1 and the answer box in P27. Could you tell me how to modify the code to allow for this?

However thanks so much for your help with this question.

All sorted, with an extra. Clicking columns C,D,E, etc to cancel will clear the result cells, clicking in column A will stop adding new cells, but still retain the old result.

Please Login or Register  to view this content.

Brilliant! Thank you so much for all of your help. It is working perfectly.

Until the next excel question...