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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|