Use the value inside a cell as variable for a function

Question

Hi, I would like to create a dynamic function in excel (no VBA) to pass the value of a variable to a number of similar functions. Example: instead of doing: =sum(a1:z1) =sum(a2:z2) =sum(a3:z3) and then rewrite all functions to do: =sum(a1:t1) =sum(a2:t2) =sum(a3:t3)

I would like to do: =sum(a1:"var"&1) =sum(a2:"var"&2) =sum(a3:"var"&3) where I could define var=z the first time and var=t the second time.

do you know if it is possible?

thanks a lot for your help rob

Answer 1

The best I could come up with. Whatever is in D3 is your "var" (put the letter of your column), the 6 is your hardcoded column (1, 2, 3, etc)

=SUM(A1:INDIRECT((CONCATENATE(INDIRECT("D3"),6))))