CODEWITHSUNDEEP
excelexcel-formula
danjswade
danjswade

Reputation: 567

Use a variable in an Excel formula

Is it possible to use a specific cell as a variable in an excel formula so whatever value is in that cell will be inserted to the formula? For example:

=SUM(B1:B100)

I want to use cell A2 as my holder, so I insert the end figure there A2 = 95. How do I essentially write this:

=SUM(B1:B(A2))

So I can leave the formula the same but change the range by updating the value in A2

Upvotes: 0

Views: 934

Answers (2)

zipa
zipa

Reputation: 27869

INDIRECT will do it:

=SUM(B1:INDIRECT("B1:B"&A2))

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34180

I suggest a good way of doing this is to use INDEX. Because INDEX returns a reference, you can use it to specify a range like this

=SUM(B1:INDEX(B:B,A2))

Although OFFSET and INDIRECT can be used, there are certain disadvantages with these volatile functions

Upvotes: 1

Related Questions