Set Data Range by Variable

[MJSlattery]

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]

[Domenic]

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!

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]

[Peo Sjoblom]

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" <[email protected]> wrote
in message news:[email protected]...[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
>

[Jim Cone]

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" <[email protected]>
wrote in message news:[email protected]...

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]

Very helpful and explicit advice.

Michael