Re: [MLUG - DISCUSSION] Excel tricks

["Jonathan King" <EMAIL:PROTECTED>]

On 11/8/07, Mike Miller <EMAIL:PROTECTED> wrote:
> I want to do this in Microsoft Excel but I'll also want to do it in Calc
> (Open Office), so feel free to tell me the answer in Calc terms, if you
> happen to know the answer and are willing to share.
>
> I want to use a value in a cell to determine a range for a function.  I
> thought I used to do this in Lotus 1-2-3 20 years ago, but I'm not sure.
> So I think it is doable.  For example, I might want to sum the values in
> column B from row 1 to row 'X' where X equals the value in cell A1.  Any
> ideas?

Lord help me; I know the answer to this question. I have too
thoroughly mocked that which I have become. The answer is in the handy
"indirect" function. So let's assume a1 has the ending row number, b
is the row to sum (from b1) and c1 is where you want the formula to
pop up. Then what you want in c1 is:

=sum(indirect("b1:b"&a1))

There are probably other ways to do this as well.

jking

_______________________________________________
discussion mailing list
EMAIL:PROTECTED
http://mlug.missouri.edu/mailman/listinfo/discussion