Let's say that cells A1, A2, A3, A4, and A5 all contain the numbers 1, 2, 3, and 5, respectively. We compute the total of A1:Ax in cell A7. In cell B1, x is mentioned (in this case, x can be any number from 1 to 6). You can enter the formula below in cell A7:
CONCATENATE will give you the index of the cell Ax(if you put 3 in B1, CONCATENATE("A",B1)) gives A3).
INDIRECT convert "A3" to an index.