Forum Discussion
Mertijn Vermeulen
Jun 12, 2018Copper Contributor
Excel with variable cell selection
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?
- Matt MickleBronze Contributor
Hey Mertijn-
try using a formula like this (See attached .xlsx example file for reference):
=SUM(INDIRECT("D2:D"&B3))
- BWatFXBGCopper Contributor
Matt Mickle thanks guys that helped me complete this very useful formula:
=UNIQUE(INDIRECT("A2:A"&COUNTA(A:A)))
It could be
=SUM(OFFSET($A$1,0,0,5))
where instead of 5 you may use reference on result of your calculations