How to 'Calculate' an Excel Reference
 

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?

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


--
Regards,
Dave


"Tightlines" wrote:

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?

Try:

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

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Tightlines" wrote in message
...
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?

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)?

"Tightlines" wrote:

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?

=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.

"Tightlines" wrote:

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)?

"Tightlines" wrote:

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?

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

--
Regards,
Dave


"Tightlines" wrote:

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)?

"Tightlines" wrote:

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?

Another way:

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

--
HTH,

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


"Tightlines" wrote in message
...
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)?

"Tightlines" wrote:

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?