Formula that contains cell reference built from string

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

Here's my newbie question o' the day:

Is is possible to replace a cell reference in a formula
with the result of a string concatenation?

For example:

Cell A1 contains the last row of my data table (which is
100). I have a formula like this:

=Sum(B1:B100)

I'd like instead to do something like:

=Sum(B1:B&A1)

How could I do that?

tod
 
=SUM(INDIRECT("B1:B" & A1))
The indirect function allows you to use variables
in specifying ranges.

Dan E
 
Tod,

Another method would be to use OFFSET

=SUM(OFFSET($B$1,0,0,A1))

If you are using a formula in A1 to determine the last row of data, you
could replace the reference to A1 with that formula.

PC
 
Back
Top