using a formula to get the row part of a range

[jimblimm]

I would like to know if there is a way to reference another cell to get starting range

A1 = 100

B1 = range

Is there anyway to make B1 calculate instead of B1: b200
Instead of B1, I would like the range to reference the number in a1,,,100
So my range will change if a1 changes auto automatically

B100:b200

I know this formula won't work but the concept is like

=if(b(concatenate(a1):b200
The b(concatenate (a1) would yield 100 which in term would make formula display b100: b200

 

[pgc01]

Hi Jim

Try:

INDEX(B:B,A1):B200

Ex.:

=SUM(INDEX(B:B,A1):B200)