Excel with variable cell selection

Occasional Visitor

Hello,

 

I have a list of numbers, and I want to make a formula to choose how much of that list is needed.

For example, I want to choose A1 to A5, but the number 5 is from an other calculation. So if I change something in that calculation, the number 5 changes as well. And instead of the number 5 I now need 7. I do not want to change it everytime manually because it would take some work.

=SUM(A1:A(VARIABLE))

What is the function to make that second cell variable?

4 Replies

It could be

=SUM(OFFSET($A$1,0,0,5))

where instead of 5 you may use reference on result of your calculations

Hey Mertijn-

 

try using a formula like this (See attached .xlsx example file for reference):

 

=SUM(INDIRECT("D2:D"&B3))

 

SumIndirect.png

Added OFFSET

image.png

 

@Matt Mickle   thanks guys that helped me complete this very useful formula:

=UNIQUE(INDIRECT("A2:A"&COUNTA(A:A)))