Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Set Data Range by Variable
I want to be able to define the number of cells in a column that will be included in a calculation. =SUM(A5:A25)/2 So in the above example I have selected 20 cell in column A. I want to be able to set the number via a slider on the spread sheet to capture any number of cell below the cell with the formula in it. So, and I know this does not work but, =SUM(A5:A(X))/2 where X equals the number of cells that I want included. I want to avoid VBA because this will update a graph and I don't want to run a macro every time the data gets changed to update my graph. I have gone through the Excel 2003 Bible, called MS Office support and combed the web. There must be a simple way to accomplish this without a macro. Thanks to all. Michael [color=DarkRed] -- MJSlattery ------------------------------------------------------------------------ MJSlattery's Profile: http://www.excelforum.com/member.php...o&userid=16141 View this thread: http://www.excelforum.com/showthread...hreadid=377967 |
#2
|
|||
|
|||
Try... =SUM(A5:INDEX(A5:A65536,B5)) ...where B5 contains the number of cells you want included. Then link B5 to your slider. Hope this helps! MJSlattery Wrote:[color=blue] I want to be able to define the number of cells in a column that will be included in a calculation. =SUM(A5:A25)/2 So in the above example I have selected 20 cell in column A. I want to be able to set the number via a slider on the spread sheet to capture any number of cell below the cell with the formula in it. So, and I know this does not work but, =SUM(A5:A(X))/2 where X equals the number of cells that I want included. I want to avoid VBA because this will update a graph and I don't want to run a macro every time the data gets changed to update my graph. I have gone through the Excel 2003 Bible, called MS Office support and combed the web. There must be a simple way to accomplish this without a macro. Thanks to all. Michael -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=377967 |
#3
|
|||
|
|||
A couple of ways
=SUM($A$5:INDEX(A:A,F1)) =SUM(A5:INDIRECT("A"&F1)) =SUM(OFFSET($A$5,,,F1-ROW($A$5)+1)) the first is to prefer since it is not volatile F1 holds the X numbers -- Regards, Peo Sjoblom (No private emails please) "MJSlattery" wrote in message ...[color=blue] I want to be able to define the number of cells in a column that will be included in a calculation. =SUM(A5:A25)/2 So in the above example I have selected 20 cell in column A. I want to be able to set the number via a slider on the spread sheet to capture any number of cell below the cell with the formula in it. So, and I know this does not work but, =SUM(A5:A(X))/2 where X equals the number of cells that I want included. I want to avoid VBA because this will update a graph and I don't want to run a macro every time the data gets changed to update my graph. I have gone through the Excel 2003 Bible, called MS Office support and combed the web. There must be a simple way to accomplish this without a macro. Thanks to all. Michael -- MJSlattery ------------------------------------------------------------------------ MJSlattery's Profile: http://www.excelforum.com/member.php...o&userid=16141 View this thread: http://www.excelforum.com/showthread...hreadid=377967 |
#4
|
|||
|
|||
Michael,
Piece of cake... 1. Add a scroll bar from the Forms toolbar to the sheet. 2. Format the scrollbar so the minimum is 0 and the increment is 1. Specify a cell link cell. (say D1) 3. Move the scroll bar over the top of the linked cell. 4. In a cell below the scrollbar, enter the formula "= D1 +1" 5. Add a title above it called "Total Cells" 6. In a cell further down enter the formula "=SUM(A1:OFFSET(A1,D1,0))" 7. Add a title above it called "Sum" 8. Enter values in column A, click the scrollbar. Regards, Jim Cone San Francisco, USA "MJSlattery" wrote in message ... I want to be able to define the number of cells in a column that will be included in a calculation. =SUM(A5:A25)/2 So in the above example I have selected 20 cell in column A. I want to be able to set the number via a slider on the spread sheet to capture any number of cell below the cell with the formula in it. So, and I know this does not work but, =SUM(A5:A(X))/2 where X equals the number of cells that I want included. I want to avoid VBA because this will update a graph and I don't want to run a macro every time the data gets changed to update my graph. I have gone through the Excel 2003 Bible, called MS Office support and combed the web. There must be a simple way to accomplish this without a macro. Thanks to all. Michael MJSlattery |
#5
|
|||
|
|||
Very helpful and explicit advice. Michael -- MJSlattery ------------------------------------------------------------------------ MJSlattery's Profile: http://www.excelforum.com/member.php...o&userid=16141 View this thread: http://www.excelforum.com/showthread...hreadid=377967 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Selecting data according to date range | Excel Discussion (Misc queries) | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
Is there a way of attaching data labels from a text range to dat. | Charts and Charting in Excel | |||
Do Pivot Tables have an automatic data range expansion? | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |