Question about Variables and Specifying Cell Address

[Majestyk]

I need to allow a user to specify two numbers, so we'll call them var1 and var2. I need to do a manipulation on each number, which results in another number. Once that is done, I need to do a SUM of a series of rows bounded by those two numbers.

In other words, I need to do something like:

=SUM(B(var1):B(var2))

Where the cell reference is literally B (which is the column where a long series of numbers is located) and one or the other number I generated from the user-solicited variable. The literal output would be something like:

=SUM(B20:B30)

But I need to specify a number in place of the 20 or 30 that is generated from user inputs. What is the best way to achieve this?

[RickRandom]

The OFFSET() function allows you to specify a range to be used in another function such as SUM().

Something like:

=SUM(OFFSET(....))

No time to work out the details - work calls

[bobban]

This should get you started:

Assume the user input var1 is in cell F4 and var2 is in cell G4. Your data is in column C and you need to make a Sum from this. This will do the trick:

=SUM(INDIRECT(ADDRESS(F4;3)):INDIRECT((ADDRESS(G4;3))))

What that is doing is making an ADDRESS (as text), by getting the row value for the address from F4, and fixing the column value for the address as '3'(or column C). So say the user input 8 into the var1 cell, the result would be $C$8, and this is your lower bound. similarly, if var2 was input as 17, then the second ADDRESS would be $C$17. INDIRECT is simply returns the reference specified by a text string, which is what was made by ADDRESS. The SUM I am sure you know. Not sure if this is the best but it works well for me so far.

Edited: Originally I said 'you data in column' B, and then used '3'(or column C) as the second argument in the ADDRESS function. This should have 'your data in column C', so I just changed that.
_________________
nil sine labore

[Majestyk]

Excellent. I will work with that and see what I get. Thanks for the help!

[Majestyk]

That worked perfectly. Thanks a lot.