Sum a Range based on Cell Value Entered 2

[SanAntonioSpurFan]

In Excel, I need a formula that will lookup the value entered in cell A1, then sum the values of B1:B whatever the number entered in cell A1 was and place the result in Cell C1.

For example: A user entered then value 20 in cell A1. Cells B1:B500 have data. I want a formula that will automaticlly populate cell C1 with the sum of Cells B1:B20. If the user entered 18 the formula would sum B1:B18.

How do you do this?

 

[PHV]

Take a look at the INDIRECT function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SanAntonioSpurFan]

I'm not sure my question was very clear. If a user enters a given number in cell A1, I need a formula in cell C1 that automaticlly uses the sum function to sum the values in column B. The sum function will always start at cell B1 but the ending cell will be dependant upon the value entered by the end user.

If the number 5 was entered the formula would be sum(b1:b5) if the number 20 was entered the formula would be sum(b1:b20)

What do I need to type in cell c1 to make this logic work, or do I need some code?

 

[TonyJollans]

Hi SanAntonioSpurFan,

I think your question was clear (but in the wrong forum as it is not a VBA question) - as was the answer.

Have you looked at the INDIRECT Function? It is exactly what you need. It allows you to specify a dynamic range as a string value, for example

[blue][tt] INDIRECT("B1:B"&A1)[/tt][/blue]

Another way would be to use the OFFSET Function, but INDIRECT is probably easier.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

In C1:
=SUM(OFFSET(B1:B1,0,0,INDIRECT("A1"),1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

PH,

Offset AND Indirect - no need for both!

[blue][tt] =SUM(OFFSET(B1,0,0,A1,1))[/tt][/blue] will do the job

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

Good catch Tony.
Seems I should ignore formula questions in vba forum ...

 

[TonyJollans]

No - carry right on

You got it right first time with INDIRECT all by itself. I confused the issue by mentioning OFFSET.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.