How to 'Calculate' an Excel Reference

[Guest]

I'm trying to determine how I can easily change a cell reference in a formula
to a different value based on a calculation in my spreadsheet.

For example:

I have the formula Sum(A1:A10)

In reality, I would like the formula to change depending on the value in
another cell (say cell B1) so that when B1 = 54, the formula becomes
Sum(A1:A54). Is there a way I can change the reference in the formula in
this manner using cell values or constants?

 

[Guest]

=SUM(INDIRECT("A1:A"&B1))

 

[Sandy Mann]

Try:

=SUM(INDIRECT("A1:A"&B1))

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

Thanks,

That helped - how would it work if I wanted to make both of the references
in the formula using the INDIRECT function? (so I would have A1 determined by
a value in B1 and A10 determined by a value in B2)?

 

[Guest]

=SUM(INDIRECT("A"&B1&":A"&B2))

Just look at what the function is doing and you should be able to figure it
out.

From Help: "[Indirect] Returns the reference specified by a text string."

The "&" charector is how you combine text strings.

So you are just taking the values in B1 and B2 and converting them to text
strings to get the row references of the list. When you see a new formula,
look it up in the help to better understand what's going on. This helps you
to learn how to use the function properly and should answer any other
questions you have.

 

[Guest]

=SUM(INDIRECT("A"&B1&":A"&B2))

 

[RagDyer]

Another way:

=SUM(INDEX(A:A,B1):INDEX(A:A,B2))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================