Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
=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? |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
=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? |
#6
|
|||
|
|||
=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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to calculate integrals with Excel? | Excel Worksheet Functions | |||
Does Excel use least squares regression to calculate trendlines? | Charts and Charting in Excel | |||
Excel should have a absolute reference cell property. | Excel Discussion (Misc queries) | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions | |||
using a reference in vlookup with excel 2003 | Excel Worksheet Functions |