Link references to cell values...

I

imakaveli

New Member
Joined
Sep 8, 2009
Messages
20
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
 
Sort by date Sort by votes
MrKowz said:
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><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 rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th>Sheet1</th></tr></thead></table>

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><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 style=" background-color: #E0E0F0;color: #161120" width="10px">C1</th><td style="text-align:left">=SUM(OFFSET(A1,0,0,B1,1))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">G1</th><td style="text-align:left">=SUM(INDIRECT("A"&E1&":A"&F1))</td></tr></tbody></table></td></tr></tbody></table>
Click to expand...


Very nice thank you!!
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
MrKowz said:
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.
Click to expand...
So why didn't you mention that same 'boogeyman" when you suggested using OFFSET? ;)
 
Upvote 0
imakaveli said:
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
Click to expand...
Try this...

=SUM(A1:INDEX(A1:A100,C1))

Where C1 is the row variable.

Note that if C1 is an empty cell the formula will calculate the ENTIRE referenced range.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create a link to another workbook via form control
Replies
1
Views
73
Micron
M
A
  • Question Question
Add a link into a cell
Replies
4
Views
108
AlexanderBB
A
C
  • Question Question
How to merge the data in a number of cells in a list based on a reference
Replies
9
Views
211
Peter_SSs
Peter_SSs
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
210
datatronics505
datatronics505
M
  • Question Question
2-Way Cell Referencing
Replies
1
Views
175
jkpieterse
jkpieterse

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top