Using CTRL to select cells for a formula

[Elgee]

Hi -
I tried to search for help on this one before posting - but wasn't sure how
to search for my answer if that makes sense.

Essentially - I work with a very manual spreadsheet with a lot of various
data for project reporting. (Forecasts, Actuals, EAC, Variance etc.) Due to
the format / layout of the worksheet - I often need to use 'ctrl' to select
the cells I want to include in a formula. IE: =sum(f1, f3, f33, f35) etc.
Is it possible to back into a formula like that? IE: I click CTRL and
select the various cells I want to total and I can see that # in the status
bar at the bottom of the screen. Is there a way to copy those cells into a
formula?

Thanks!

 

[Sheeloo]

I don't think you can select some cells and then pass them onto any formula
chosen by you.

What you want, however, can be achieved with a simple macro as follows;
(Before running this macro select the cells you want and then run it [macro
can be assigned to a button or keyboard short cut]

Currently it calculates total, average and count of cells and puts them in
cells D1-D3. You may replace the logic within the loop to get other results.

Sub Cells_Loop()
Dim i As Range
Dim SumSelectedCells

SumSelectedCells = 0

For Each Cell In Selection
SumSelectedCells = SumSelectedCells + Cell.Value
Next
AverageSelectedCells = (SumSelectedCells / Selection.Count)
Range("D1").Value = SumSelectedCells
Range("D2").Value = SumSelectedCells / Selection.Count
Range("D3").Value = Selection.Count
'MsgBox ("TOTAL=" & SumSelectedCells & ", AVEARAGE=" & AverageSelectedCells
& ", COUNT=" & Selection.Count)
End Sub

 

[Joerg Mochikun]

Elgee, I'm not sure what your problem is.
All formulas that accept multiple cells as arguments (SUM, MIN, AVERAGE
etc.) work the way you describe: After entering e.g. '=SUM(' you can
CRTLclick your way through the worksheet and Excel would add the cells to
your formula. Entering the closing ')' completes the formula. What means
"back into a formula like that"?. Do you want to reuse the cell addresses
included in your SUM formula? In that case it would be easier to CTRLclick
the needed cells and give this range a name (in the upper left "Name Box"
where you normally see the cell address of the active cell). Then you can
use this range name in you formulas (e.g. =SUM(Forecasts)). Having the sum
appear in the status bar is already a standard function of Excel. But again
I'm not sure if I understood you correctly (what would be "that number"? The
sum, the cell addresses, the cell values?).

JM

I don't think you can select some cells and then pass them onto any formula
chosen by you.

What you want, however, can be achieved with a simple macro as follows;
(Before running this macro select the cells you want and then run it
[macro
can be assigned to a button or keyboard short cut]

Currently it calculates total, average and count of cells and puts them in
cells D1-D3. You may replace the logic within the loop to get other
results.

Sub Cells_Loop()
Dim i As Range
Dim SumSelectedCells

SumSelectedCells = 0

For Each Cell In Selection
SumSelectedCells = SumSelectedCells + Cell.Value
Next
AverageSelectedCells = (SumSelectedCells / Selection.Count)
Range("D1").Value = SumSelectedCells
Range("D2").Value = SumSelectedCells / Selection.Count
Range("D3").Value = Selection.Count
'MsgBox ("TOTAL=" & SumSelectedCells & ", AVEARAGE=" &
AverageSelectedCells
& ", COUNT=" & Selection.Count)
End Sub

Hi -
I tried to search for help on this one before posting - but wasn't sure
how
to search for my answer if that makes sense.

Essentially - I work with a very manual spreadsheet with a lot of various
data for project reporting. (Forecasts, Actuals, EAC, Variance etc.)
Due to
the format / layout of the worksheet - I often need to use 'ctrl' to
select
the cells I want to include in a formula. IE: =sum(f1, f3, f33, f35)
etc.
Is it possible to back into a formula like that? IE: I click CTRL and
select the various cells I want to total and I can see that # in the
status
bar at the bottom of the screen. Is there a way to copy those cells into
a
formula?

Thanks!