Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you select visible cells only by default, without Ctrl+G. | Excel Discussion (Misc queries) | |||
CTRL+select Excel 2007 | Excel Worksheet Functions | |||
does ctrl f select cells randomly? | Excel Discussion (Misc queries) | |||
ctrl select not working in 07 | Excel Discussion (Misc queries) | |||
Ctrl Key to Select Cells | Excel Discussion (Misc queries) |