How to use variable in reference

[Ming]

Hello,

I am wondering if anyone can help me on the use of variable in reference.

As an example, A22 refers to a cell. how can I replace 22 with a variable,
say bb? In this way, I can write a function like SUM(A1:A22) with
SUM(A1:Abb). I need this because I have a lot of exl file. In each file, the
range of sum is different. One
is from A1:A22 while another from A1:A77.

Thanks,

 

[Roger Govier]

Try
=SUM(A1:INDIRECT("A"&B1))
Where B1 holds your variable e.g. 22

 

[Bob Phillips]

=SUM(INDIRECT("A1:A"&B1)

where B1 holds that value

--

HTH

RP
(remove nothere from the email address if mailing direct)