starting range for an array

[jimblimm]

I have a formula

=COUNTIF('MAIN DATA'!$BS$20:$BU$4000,"15")


i want to be able to get my starting column/row number with the index feature

INDEX('MAIN DATA'!B:B,A1):'MAIN DATA'!$B4000

a1=2354

example 'MAIN DATA'!bs2354:
just cant figure it out

 

[Jerry Sullivan]

You can build a range reference string using the INDIRECT() function.

If the values in a1 = 2354 and b1= "bs"
and the formula you want to build is this....
=INDEX('MAIN DATA'!BS2354:BU4000,2,3)

You can get the same result using this formula....
=INDEX(INDIRECT("'MAIN DATA'!" & B1 & A1& ":BU4000"),2,3)

INDIRECT has some drawbacks, so it's best to use when there is not a better alternative.