Link references to cell values...

[imakaveli]

Hello guys, sorry for the title but I don't know how to explain the problem even if it's pretty easy...

Basically I would like to know a way to link a reference in one cell to a value in another cell...

EXAMPLE: if in one cell I have the formula SUM(A1, A100) but I want the end of the sum (100) to change when I change a value in a cell, if for example I put 50 in a cell I want the sum to be from A1 to a 50... basically link the number of the reference to a number in a cell so that when I change the number in that cell I also change the sum...

I am so sorry if the explanation was not that clear but I really dunno how to explain the problem in an easier way!!!

Thank you for any help
Ciao
iMak

 

[MrKowz]

Assuming you are placing that value in B1:

=SUM(OFFSET($A$1,0,0,B1,1))

 

[imakaveli]

Assuming you are placing that value in B1:

=SUM(OFFSET($A$1,0,0,B1,1))

Thank you very much for your answer MrKowz, I will try it!!

 

[pagrender]

Another option is to use the Indirect function.

If your data is in column A, and you want to sum the range A1 to A???, enter a 1 in B1 and whatever row you would like the formula to end in C1.

For example, if you want your formula to be A1:A3, enter a 1 in B1 and a 3 in C1.

Here is the formula:
=SUM(INDIRECT("A"&B1&":A"&C1))

 

[MrKowz]

Another option is to use the Indirect function.

If your data is in column A, and you want to sum the range A1 to A???, enter a 1 in B1 and whatever row you would like the formula to end in C1.

For example, if you want your formula to be A1:A3, enter a 1 in B1 and a 3 in C1.

Here is the formula:
=SUM(INDIRECT("A"&B1&":A"&C1))

If you need to define the first cell and the last cell by referencing to other cells, then INDIRECT will be the way to go. However, due to INDIRECT being volatile (calculates at every calculation event rather than when its precedents are changed), it can cause the workbook to slow down if used excessively.

 

[pagrender]

If you need to define the first cell and the last cell by referencing to other cells, then INDIRECT will be the way to go. However, due to INDIRECT being volatile (calculates at every calculation event rather than when its precedents are changed), it can cause the workbook to slow down if used excessively.

I didn't know that... thanks for the tip!

 

[imakaveli]

Another option is to use the Indirect function.

If your data is in column A, and you want to sum the range A1 to A???, enter a 1 in B1 and whatever row you would like the formula to end in C1.

For example, if you want your formula to be A1:A3, enter a 1 in B1 and a 3 in C1.

Here is the formula:
=SUM(INDIRECT("A"&B1&":A"&C1))

Guys it says that the formula contains an error... maybe is a matter of "?

 

[imakaveli]

Guys it says that the formula contains an error... maybe is a matter of "?

My bad, it was right!!! Thank you!

 

[MrKowz]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=SUM(<font color="Blue">OFFSET(<font color="Red">A1,0,0,B1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=SUM(<font color="Blue">INDIRECT(<font color="Red">"A"&E1&":A"&F1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[MrKowz]

I didn't know that... thanks for the tip!

My bad, it was right!!! Thank you!

Thanks for the feedback.