A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using CTRL to select cells for a formula



 
 
Thread Tools Display Modes
  #1  
Old August 27th, 2008, 12:16 AM posted to microsoft.public.excel.worksheet.functions
Elgee
external usenet poster
 
Posts: 26
Default Using CTRL to select cells for a formula

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!
  #2  
Old August 27th, 2008, 12:54 AM posted to microsoft.public.excel.worksheet.functions
Sheeloo
external usenet poster
 
Posts: 797
Default Using CTRL to select cells for a formula

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


"Elgee" wrote:

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!

  #3  
Old August 27th, 2008, 08:30 AM posted to microsoft.public.excel.worksheet.functions
Joerg Mochikun
external usenet poster
 
Posts: 94
Default Using CTRL to select cells for a formula

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



"Sheeloo" wrote in message
...
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


"Elgee" wrote:

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!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.