INDIRECT not working as expected - referencing multiple dynamicranges
 

Hi all

I have some dynamic ranges that are subsets of a master dynamic
ranges. The criteria for the start/end of each subset range are in
A1:A4

dynMaster = OFFSET($A$40, 0, 0, NumRows)
dynSub1 = INDEX(dynMaster, MATCH($A$1, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$2, dynMaster, 0)-1)
dynSub2 = INDEX(dynMaster, MATCH($A$2, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$3, dynMaster, 0)-1)
etc

These work fine. But now I want to be able to use a formula that
refers to the different subset data sources according to which column
the formula is used in.

DataSource = INDIRECT("dynSub" & COLUMN())

This doesn't work at all and I'm getting a REF error. I've noticed,
however, that if I hardcode the reference, ie INDIRECT("dynSub1"), it
works only when it's in the same row as dynSub1, but not where I need
to use the formula. Any suggestions are greatly appreciated.

TIA

Paul Martin
Melbourne, Australia